• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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?

Thanks
0
Dovberman
Asked:
Dovberman
  • 2
  • 2
  • 2
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Starting in 12c, Oracle has introduced this capability:
http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php

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;
0
 
DovbermanAuthor Commented:
Thanks,

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.

Thanks
0
 
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now