CODEWITHSUNDEEP
sqloracle-databaserankrownum
Kevin LaBranche
Kevin LaBranche

Reputation: 21088

Is using rownum with a presorted result good or should rank be used?

I'm writing an Oracle query that needs to get the most recent event based on the date it occurred.

I have two queries that appear to be working correctly in my test cases.

The first uses a subquery to get the events in date order and then I just pull the first record via rownum: 

    SELECT description FROM
    (
     SELECT description FROM tablename
     WHERE ((event_type IN ('A','I','Y')) AND (meeting_date IS NOT NULL) 
     AND id='whatever') 
     ORDER BY meeting_date DESC
    )
    WHERE rownum = 1

The second one uses rank to accomplish the same result:

SELECT description FROM
(
SELECT description, RANK() OVER( ORDER BY meeting_date DESC) mtg_rank 
 FROM tablename 
 WHERE ((event_type IN ('A','I','Y'))  AND (meeting_date IS NOT NULL) 
 AND id= 'whatever') 
)
WHERE mtg_rank = 1

To me the presorted rownum is simple enough and I would go with it. I realize that rownum works before a sort which is why I did the ordering in a subselect first.

However, I'm not sure if I'm missing something with that thought?

I am also wondering if rank in this case is the preferred/best practice or perhaps is better at conveying the intent of the query?

Upvotes: 2

Views: 662

Answers (1)

Justin Cave
Justin Cave

Reputation: 231771

Except for cases of ties (which Brian's comment discusses quite well), both queries will work and will return the same results.

Personally, I would much prefer the analytic function approach because it is much more adaptable. You can adjust how ties are handled by swapping between the RANK, DENSE_RANK, and ROW_NUMBER analytic functions. You can also do things like add a PARTITION BY clause to return the most recent meeting for each ID if you want to run the query for multiple ID values rather than just one.

Upvotes: 4

Related Questions