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
beechersAsked:
Who is Participating?
 
beechersAuthor Commented:
Thank you for your response sgroppenbecker.  I could not get the Top n * statement working but that could be due to using Oracle.

However, I was able to get it working using the following code:

SELECT * 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)
 WHERE ROWNUM<=10
0
 
Scott CraigWebmasterCommented:
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

0
 
beechersAuthor Commented:
I get the following error when running this in toad:

ORA-00923: FROM keyword not found where expected
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
beechersAuthor Commented:
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.
0
 
awking00Commented:
In Oracle use row_number() function - see this link.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
0
 
awking00Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.