CODEWITHSUNDEEP
sqloracle-database
Razgriz231
Razgriz231

Reputation: 105

Oracle - order by in subquery of union all

I have a query where the individual selects are pulling the most recent result from the table. So I'm having the select order by id desc, so the most recent is top, then using a rownum to just show the top number. Each select is a different place that I want the most recent result.

However, the issue I'm running into is the order by can't be used in a select statement for the union all.

select 'MUHC' as org, 
       aa, 
       messagetime 
  from buffer_messages 
 where aa = 'place1' 
   and rownum = 1 
 order by id desc
union all 
select 'MUHC' as org, 
       aa, 
       messagetime 
  from buffer_messages 
 where aa = 'place2' 
   and rownum = 1
 order by id desc;

The each select has to have the order by, else it won't pull the most recent version. Any idea's of a different way to do this entirely, or a way to do this with the union all that would get me the desired result?

Upvotes: 1

Views: 2974

Answers (3)

Amit Kumar
Amit Kumar

Reputation: 2231

For using ORDER BY with UNION ALL in all sub queries, we can use below query.

SELECT * FROM (
  SELECT 'MUHC' AS org, aa, messagetime
  FROM buffer_messages 
  WHERE aa = 'place1' AND rownum = 1
  ORDER BY id desc
)
UNION ALL
SELECT * FROM (
  SELECT 'MUHC' AS org, aa, messagetime
  FROM buffer_messages 
  WHERE aa = 'place2' AND rownum = 1
  ORDER BY id desc
)

Upvotes: 2

Nick Krasnov
Nick Krasnov

Reputation: 27251

By putting where .. and rownum = 1 condition before order by clause you wont produce desired result because the result set will be ordered after the where clause applies, thus ordering only one row in the result set which can be whatever first row is returned by the query.

Moreover, putting order by clause right before the union all clause is semantically incorrect - you will need a wrapper select statement.

You could rewrite your sql statement as follows:

select *
  from ( select 'MUHC' as org
               , aa 
               , messagetime 
               , row_number() over(partition by aa
                                       order by id desc) as rn
           from buffer_messages 
        ) s
where s.rn = 1

And here is the second approach:

select max('MUHC')                                         as org
     , max(aa)                                             as aa
     , max(messagetime) keep (dense_rank last order by id) as messagetime 
 from buffer_messages
group by aa

Upvotes: 0

Vijay
Vijay

Reputation: 1034

Try this

select 'MUHC' as org, 
   aa, 
   messagetime 
from buffer_messages bm
where aa = 'place1' 
and id= (Select max(id) from buffer_messages where aa = 'place1'  )
union all 
select 'MUHC' as org, 
   aa, 
   messagetime 
from buffer_messages 
where aa = 'place2' 
and id= (Select max(id) from buffer_messages where aa = 'place2'  )

Upvotes: 3

Related Questions