2 Replies Latest reply: Jan 2, 2013 1:36 AM by Marwim RSS

    select extra row without using UNION ALL in pl/sql

    982317
      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
          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
            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