This discussion is archived
2 Replies Latest reply: Jan 1, 2013 11:36 PM by Marwim RSS

select extra row without using UNION ALL in pl/sql

982317 Newbie
Currently Being Moderated
Hi,
Can anyone tell me how to select extra row without using UNION or UNION ALL in pl/sql. Actually I want to have my o/p of query as partitioned by designation and ordered by salary and than one extra row which will contain the highest salary in a particular salary. My table has first_name,emp_id,designation and salary column. And I wnt the o/p as.

Mohinish,12212,SI,46000
Ram,11212,SSI,47000
Shyam,12133,SI,48000
Rick,9898,SI,46000
Rocky,12312,SSI,56000
Sariq,23948,SI,43000
Suman,12789,HR,49000
Sampy,12780,SI,46000
Parna,11111,HR,50000

Now the o/p should be.

Mohinish,12212,SI,46000
Rick,9898,SI,46000
Sariq,23948,SI,43000
Shyam,12133,SI,48000

Shyam,12133,SI,48000

Ram,11212,SSI,47000
Rocky,12312,SSI,56000

Rocky,12312,SSI,56000

Suman,12789,HR,49000
Parna,11111,HR,50000

Parna,11111,HR,50000

Thanks in Advance
  • 1. Re: select extra row without using UNION ALL in pl/sql
    MLBrown Journeyer
    Currently Being Moderated
    You don't have to do a UNION or UNION ALL in PL/SQL but you would need to in SQL to get the desired output:
    with data_recs 
     as (select 'Mohinish' first_name,12212 emp_id,'SI' designation,46000 salary from dual union
         select 'Ram',11212,'SSI',47000 from dual union
         select 'Shyam',12133,'SI',48000 from dual union
         select 'Rick',9898,'SI',46000 from dual union
         select 'Rocky',12312,'SSI',56000 from dual union
         select 'Sariq',23948,'SI',43000 from dual union
         select 'Suman',12789,'HR',49000 from dual union
         select 'Sampy',12780,'SI',46000 from dual union
         select 'Parna',11111,'HR',50000 from dual)
    select first_name, emp_id, designation, salary from data_recs union all
    select s.first_name, s.emp_id, s.designation, s.salary
      from (select first_name,
                   emp_id,
                   designation,
                   salary,
                   row_number() over (partition by designation order by salary desc) high_salary
              from data_recs
             order by designation, salary) s
     where s.high_salary = 1
     order by designation, salary;
    FIRST_NAME  EMP_ID DESIGNATION   SALARY
    ---------- ------- ----------- --------
    Suman        12789 HR             49000 
    Parna        11111 HR             50000 
    Parna        11111 HR             50000 
    Sariq        23948 SI             43000 
    Rick          9898 SI             46000 
    Mohinish     12212 SI             46000 
    Sampy        12780 SI             46000 
    Shyam        12133 SI             48000 
    Shyam        12133 SI             48000 
    Ram          11212 SSI            47000 
    Rocky        12312 SSI            56000 
    Rocky        12312 SSI            56000 
  • 2. Re: select extra row without using UNION ALL in pl/sql
    Marwim Expert
    Currently Being Moderated
    Hello,

    welcome to the forum.

    This is the forum for the tool {forum:id=260}. Please post your question in {forum:id=75} and read {message:id=9360002} before posting.

    Regards
    Marcus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points