Link to home
Start Free TrialLog in
Avatar of http:// thevpn.guru
http:// thevpn.guruFlag for Denmark

asked on

Get first 10 rows in Oracle select query

hi all ..what should I enter into my query to limit the select query to fetch only the first 10 rows in oracle

any examples please ?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

SELECT TOP 10 ...
hi, try this

select * from tbl where rownum<=10;
SELECT  x,y,z FROM b WHERE rownum < 10;
Avatar of http:// thevpn.guru

ASKER

what  about 10 to 30
select * from tbl where rownum between 10 and 30;
ASKER CERTIFIED SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
select empno, ename , ROWNUMB
 from (select empno, ename , row_number() over (order by empno asc) as ROWNUMB
         from emp)
  where ROWNUMB between 10 and 30;
jinesh_kamdar is right, my code will not work. but will it work without assigning an alias for rownum?
SELECT T.* FROM
(SELECT ROWNUM R,* FROM tbl WHERE ROWNUM <= 30) T
WHERE T.R >= 10;

Open in new window

You're right, neither would my query nor yours. But below would :)
SELECT TMP.* FROM
(SELECT ROWNUM R,t.* FROM tbl t WHERE ROWNUM <= 30) TMP
WHERE TMP.R >= 10;

Open in new window

hmm, why would it not work without an alias in the subquery?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> hmm, why would it not work without an alias in the subquery?

B'coz [ SELECT ROWNUM R,* FROM tbl WHERE ROWNUM <= 30 ] itself would not work.
If the SELECT has a * and any additional columns in it, then the 8 has to be referenced by the table-name / alias.
Followup. Didn't see sdstuber solution in time.  That one looks OK.
Avatar of Sean Stuber
Sean Stuber

that's what I just said.  :)
03.10.2008 at 05:11PM EET
Of course the rows have to be ordered!
This select orders (over (order by empno asc) )the rows:
 
select empno, ename , ROWNUMB
 from (select empno, ename , row_number() over (order by empno asc) as ROWNUMB
         from emp)
  where ROWNUMB between 10 and 30;
( select * from table1 where rownum < 8 ) minus ( select * from table1 where rownum < 3)
yes, schwertner you are correct, and I included an example just like yours

I wasn't disputing your answer, merely clarifying the other posts with rownum.
Your row_number example and mine are equivalent.
this is the exact sql.....

( select * from table1 where rownum < 30 ) minus ( select * from table1 where rownum < 10)
yikes!  nsanga,  that queries the same table twice why would you do that when you can get the same data with only one hit on table1.

even with the double query, you still need to include the order by in each of the sets in order to get reliable results.

I would definitely recommend using the rownum or row_number subquery posts above instead of the minus option.