Syntax in Oracle PL/SQL equivalent to TOP(x) SQL Server

This should be simple.  I am learning to write SQL statements against an Oracle database.  I need the TOP(20) rows FROM Tablename. What is the correct PL/SQL statement?

Who is Participating?
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.

slightwv (䄆 Netminder) Commented:
Starting in 12c, Oracle has introduced this capability:

Prior to that most people use ROW_NUMBER:

select col1, col2 from (
select col1, col2, row_number() over(order by some_column) rn
from tablename
where rn<=20;
DovbermanAuthor Commented:

Is this the same as
select MembID from CogMembers where rownum<=20 ORDER BY MembID DESC; ?

I found this somewhere else on an internet search.

Mark GeerlingsDatabase AdministratorCommented:
No.  In Oracle queries you cannot use both "rownum" and "order by" at the same level of the query, at least not if you want consistent results.  That's why the suggestion from slightwv has "order by" at the inner level, then "rownum" at the outer level.

In Oracle12c this may be optimized, but be aware that in prior database versions, Oracle has to fetch and sort all of the rows before returning the "top N" rows.  So, depending on the number of records involved, the available indexes, etc,, using a "top N" query can put a big performance load on the database.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
Also keep in mind that Oracle and SQL Server both store data in tables, and both support the four basic SQL verbs (select, insert, update and delete).  Beyond that, they are very different systems.  So coding styles or approaches that work well in one system may not be needed (temporary tables for example) or may not work well, or may give different results in the other system.
slightwv (䄆 Netminder) Commented:
Mark is correct.

To clarify:  rownum is a pseudo column and gets applied BEFORE any sorting.

You can probably do the same thing using rownum in an outer query and order by on the inner query but I find the data warehousing functions to be pretty efficient.

Your mileage may vary.

select * from (
select MembID from CogMembers ORDER BY MembID DESC
where rownum<=20 ;

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
DovbermanAuthor Commented:
That makes it clear. thanks
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
Oracle Database

From novice to tech pro — start learning today.

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.