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

Posted on 2014-08-07
Last Modified: 2014-08-13
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?

Question by:Dovberman
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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;

    Author Comment


    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.

    LVL 34

    Assisted Solution

    by:Mark Geerlings
    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.
    LVL 34

    Expert Comment

    by:Mark Geerlings
    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.
    LVL 76

    Accepted Solution

    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 ;

    Author Closing Comment

    That makes it clear. thanks

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now