asked on

How do I select just the top ten records

How do I select just the top 10 records from the result of a select after it includes the order by descending? I tried using where rownum <= 10 but it gives the top 10 before the "desc" on the order by clause. Here is my current select statement.

SELECT PD_EVENT.EVENT_CODE, PD_EVENT.EVENT_DATE, PD_EVENT.EVENT_LOCATION, PD_EVENT.EVENT_OUTCOME, PD_EVENT.FILE_NUM
FROM PUBDEF.PD_EVENT
WHERE PD_EVENT.FILE_NUM=20072912
ORDER BY PD_EVENT.EVENT_DATE DESC

I also thought about possibly creating a view with all the records and then performing a Top 10 on the view but was hoping there would be a better solution.
Suggestions?

Thank you,
Susan

Scott Craig

Try this:

SELECT TOP 10 * FROM (
     SELECT  PD_EVENT.EVENT_CODE, PD_EVENT.EVENT_DATE, PD_EVENT.EVENT_LOCATION, PD_EVENT.EVENT_OUTCOME, PD_EVENT.FILE_NUM
      FROM   PUBDEF.PD_EVENT
      WHERE  PD_EVENT.FILE_NUM=20072912
      ORDER BY PD_EVENT.EVENT_DATE DESC)

Open in new window

beechers

ASKER

I get the following error when running this in toad:

ORA-00923: FROM keyword not found where expected

ASKER CERTIFIED SOLUTION

beechers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

beechers

ASKER

I appreciate sgroppenbecker for responding however the Top n command I found out does not work well with Oracle. I did however neglect to include that I was using Oracle in my question.

awking00

In Oracle use row_number() function - see this link.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

awking00

You could still have used the row_number() function -
SELECT * FROM (SELECT PD_EVENT.EVENT_CODE, PD_EVENT.EVENT_DATE, PD_EVENT.EVENT_LOCATION, PD_EVENT.EVENT_OUTCOME, PD_EVENT.FILE_NUM,
ROW_NUMBER() OVER (ORDER BY PD.EVENT_DATE DESC) RN
FROM PUBDEF.PD_EVENT
WHERE PD_EVENT.FILE_NUM=20072912)
WHERE RN<=10