Home » RDBMS Server » Server Administration » SELECT First x Records
SELECT First x Records [message #370533] Thu, 16 December 1999 17:32 Go to next message
Michael Swanson
Messages: 1
Registered: December 1999
Junior Member
In SQL Server, I can:

SELECT TOP 50 * FROM TABLE ORDER BY SALES DESC

How can I do the same thing in Oracle? I don't want to return the entire recordset (this is an Internet application). I only want the first 50 records to come back.

Is there an equivalent way to accomplish this task in Oracle/SQL?

Thanks,

Michael Swanson
mike.swanson@donnelly.com
Re: SELECT First x Records [message #370543 is a reply to message #370533] Fri, 17 December 1999 06:30 Go to previous message
hmg
Messages: 40
Registered: March 1999
Member
Hi,

My solutions are different in dependence of the database version. My reference table
is table emp in scheme scott/tiger (top 6 rows).

In Oracle 8i it's possible to write:

select * from (select * from emp order by sal desc ) where rownum < 6;

In the versions before I can offer only a tricky method:

select empno, ename, -sal_minus sal 
from ( select -sal sal_minus, empno, ename from emp
       minus
       select -sal sal_minus, empno, ename from emp
       where 1 = 2 )
where rownum < 6;

explanation:
the minus operator do an implicit sort by the order of the columns in the select list.
This solution has the disadvantages that it elimates duplicates rows. Furthermore you have
to write all the columns explizit in the select list because you can't use the
"*" operator.

Bye
Previous Topic: Re: triangles(thanx HMG)
Next Topic: renaming tables, columns etc....
Goto Forum:
  


Current Time: Wed Oct 28 06:09:45 CDT 2020