1

I have a requirement wherein I intend to fetch duplicate records from Oracle DB so as to mimic scenarios wherein we have duplicate records.

So, I decided on using union_all.

select column1 from tbl where rowid in (select rowid from  tbl where rownum<=5)
union all
select column1 from tbl where rowid in (select rowid from  tbl where rownum<=5)
order by column1;
--works

However, when trying to fetch all the columns, 

select * from tbl where rowid in (select rowid from  tbl where rownum<=5)
union all
select * from tbl where rowid in (select rowid from  tbl where rownum<=5)
order by column1;
-- Doesn't work. Invalid identifier

Any suggestion on what is wrong here would be great help.

Soumya
  • 885
  • 3
  • 14
  • 29

1 Answers1

1

You can try:

  select * from (
     select * from tbl where rowid in (select rowid from  tbl where rownum<=5)
     union all
     select * from tbl where rowid in (select rowid from  tbl where rownum<=5)
    )
   order by column1;

Or explicitly list the selected columns instead of using select *:

Sorting Query Results

For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

John A
  • 71
  • 4