Saturday, May 05, 2007

Selecting most recent X records

Because Oracle looks at rownum prior to sorting, this isn't guaranteed to work:

SELECT to_char(last_test_date, 'mm-dd-yyyy hh24:mi:ss'),
       DECODE(response_time,-1,'Fail',DECODE(response_time,1,'Pass',
'Delay')) result,
       response_time,
       message,
       checksum,
       emails_received,
       services_received
FROM email_health
WHERE rownum < 20
ORDER BY last_test_date DESC

In Oracle, here is one solution:

SELECT *
FROM (
    SELECT to_char(last_test_date, 'mm-dd-yyyy hh24:mi:ss'),
           DECODE(response_time,-1,'Fail',DECODE(response_time,1,'Pass',
'Delay')) result,
           response_time,
           message,
           checksum,
           emails_received,
           services_received
    FROM   email_health
    ORDER BY last_test_date DESC
)
WHERE rownum < 20

The inner query selects and sorts the data. The outer query selects from that and then applies the rownum test.