CODEWITHSUNDEEP
sqloracle-databasesql-order-byrownum
user1930857
user1930857

Reputation:

The order by clause returns wrong results in sub query

I have the following query which is giving wrong result. I want to sort the PROCESS_START_DATE in a descending manner so that i will always get the latest PROCESS_START_DATE in my query. I ran the above query in oracle but its showing incorrect output. I want the latest PROCESS_CREATION_DATE in the descending order. For that i have written the query which is given in question. Its running absolutely fine when i remove the condition PROCESS_MONITOR.NAME = 'WORKFLOWINENGINE1'. But when i used this condition my query fails to generate the right output. I am not getting the latest PROCESS_CREATION_DATE

select * from(
select PROCESS_START_DATE
from PROCESS_MONITOR_STATISTIC JOIN PROCESS_MONITOR ON PROCESS_MONITOR.ID=PROCESS_MONITOR_STATISTIC.PROCESS_MONITOR_ID where PROCESS_MONITOR.NAME = 'WORKFLOWINENGINE1'
order by PROCESS_START_DATE desc)
where ROWNUM <= 1000

When i ran the below query i am getting the right result. But only when i used join condition then i am not getting the expected output.

select * from 
(select PROCESS_START_DATE
    from PROCESS_MONITOR_STATISTIC
    order by PROCESS_START_DATE desc)
    where ROWNUM <=10

Upvotes: 1

Views: 1112

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

Since the ORDER BY clause is applied to a subquery, not the query itself, the order is lost for the outer query. You need two ORDER BY clauses there

  • The inner ORDER BY is needed to pick correct rows with ROWNUM
  • The outer ORDER BY is needed to ensure that the query results come in the correct order.

Here is the query that should work:

select * from(
    select PROCESS_START_DATE
    from PROCESS_MONITOR_STATISTIC
    JOIN PROCESS_MONITOR ON PROCESS_MONITOR.ID=PROCESS_MONITOR_STATISTIC.PROCESS_MONITOR_ID
    where PROCESS_MONITOR.NAME = 'WORKFLOWINENGINE1'
    order by PROCESS_START_DATE desc
) inner
where ROWNUM <= 1000
order by inner.PROCESS_START_DATE desc

Upvotes: 2

Tom&#225;s
Tom&#225;s

Reputation: 555

See here for methods of getting the correct result set for top-n. Also note the "What not to do" section which is exactly what you did.

http://oracle-base.com/articles/misc/top-n-queries.php#mistake

Upvotes: 0

Related Questions