| SELECT First x Records [message #370533] |
Thu, 16 December 1999 17:32  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
