http:// thevpn.guru

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 ?

any examples please ?

SELECT TOP 10 ...

hi, try this

select * from tbl where rownum<=10;

select * from tbl where rownum<=10;

SELECT x,y,z FROM b WHERE rownum < 10;

ASKER

what about 10 to 30

select * from tbl where rownum between 10 and 30;

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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;

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;
```

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;
```

hmm, why would it not work without an alias in the subquery?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

>> 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.

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.

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;

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.

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)

( 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.

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.