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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

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
awking00Information Technology SpecialistCommented:
In Oracle use row_number() function - see this link.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
0
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.