Oracle equivalent to SQL Server SQL

[twomes]

Does anyone know what is the equivalent in Oracle to TOP or is there one available?

Thanks,

Mark

 

[jimbopalmer]

Given that we have Oracle, what does TOP do? I tried to remain child-like, all I acheived was childish.

 

[twomes]

TOP is used for example to select the top 100 records from a table.
Select TOP 100 column_name from table_name

Selects the first 100 records in a table or you can use ascend if you like to select the last 100 records etc.

 

[busybody]

Try something like:
SELECT column_name
FROM table_name
WHERE rownum < 101

but be careful about using rownum, i.e. read Oracle's
warnings on using this.

 

[busybody]

Try something like:
SELECT column_name
FROM table_name
WHERE rownum < 101

but be careful about using rownum, i.e. read Oracle's
warnings on using this.

 

[jee]

Like &quot;busybody&quot; said, using rownum can be a problem and his/her example may or may not work, depending on your data. Because the ROWNUM is assigned upon retrieval, it is assigned prior to any sorting. This is opposite to the result you would get in SQL Server using the TOP clause. In order to select employees with the highest five salaries, you have to force sorting and then apply ROWNUM condition. Here is the syntax for a top-N query where N = 5 (this syntax with the subquery works only in Oracle 8i or later):

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
   FROM
   (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
      FROM Emp
      ORDER BY NVL(Sal, 0) DESC)
   WHERE ROWNUM < 6;

Another solution is to use the new analytic functions provided in 8i. I think this may be more efficient for large tables as these functions were added specifically with data warehousing in mind. Use the RANK or the DENSE_RANK, depending on how you want to treat ties. Example:

SELECT Empno, Ename, Job, Mgr, Sal,
   RANK() OVER
      (ORDER BY SAL Desc NULLS LAST) AS Rank,
   FROM Emp
   WHERE Rank < 6;
   ORDER BY SAL Desc NULLS LAST;