Oracle FETCH FIRST 1 ROW with UNION ALL statement

Koosh_8834017 0
Viewed 3456

I'm trying to create a UNION ALL statement in SQL using the FETCH FIRST 1 ROW however when I do that it gives me an error MISSING KEYWORD

Here's what my SQL looks like:

Select * From tabl1 where Date = '04-MAR-2020' FETCH FIRST 1 ROW

UNION ALL

Select * From tabl1 where Date = '05-MAR-2020' FETCH FIRST 1 ROW

is something like this doable. I would like to keep this as a single statement rather than adding a SUBQUERY or anything of that nature.

union oracle sql
3 Answers

Just put each part in parentheses. Works in 12.2, anyway:

( Select * From tabl1 where Date = '04-MAR-2020' FETCH FIRST 1 ROW )
UNION ALL
( Select * From tabl1 where Date = '05-MAR-2020' FETCH FIRST 1 ROW )

My actual test query, for anyone interested was this:

(select   object_name 
 from     user_objects 
 where    object_type = 'TABLE' 
 order by object_name 
 fetch first 1 row only)
UNION ALL
(select   object_name 
 from     user_objects 
 where    object_type = 'VIEW' 
 order by object_name 
 fetch first 1 row only);
user_51744360

You could use row_number() instead:

select t.*
from mytable t
where date in (date '2020-03-04', date '2020-03-05')
order by row_number() over(partition by trunc(date) order by date)
fetch first 1 row with ties

row_number() puts each day in a different partition, and orders records of each day by ascending date. You can then use the row limiting clause with the with ties option to get the first record per day.

This is more efficient than the union all solution, because it scans the table only once.

If you do want union all, then you can do:

select *
from (
    (select * from tabl1 where date = date '2020-03-04' order by date fetch first 1 row)
    union all
    (select * from tabl1 where date = date '2020-03-05' order by date fetch first 1 row)
)
user_106767160

Try this

Select * From tabl1 where Date = '04-MAR-2020' and ROWNUM <= 1
UNION ALL
Select * From tabl1 where Date = '05-MAR-2020' and ROWNUM <= 1
user_132610730
Related