|
Re: first 25 rows? [message #287190 is a reply to message #287184] |
Tue, 11 December 2007 06:27   |
|
In my table i have 1 to 50 tables are there
Is that tables or rows?
If rows then you can use keyword ROWNUM in condition.
Search for ROWNUM.
Kiran.
|
|
|
Re: first 25 rows? [message #287191 is a reply to message #287184] |
Tue, 11 December 2007 06:28   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
First of all you have to define what you call "first". Oracle is a relational database. It has no idea of "first" or "last". You need to sort the result set. In the example below it is ordered by empno:
SQL> select empno
2 , ename
3 from emp
4 order by empno
5 /
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected. If I only want to see the first 10 of that output, I wrap the select in an another select and limit the output by using the ROWNUM pseudo column:
SQL> ed
Wrote file afiedt.buf
1 select *
2 from ( select empno
3 , ename
4 from emp
5 order by empno
6 )
7* where ROWNUM <= 10
SQL> /
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
10 rows selected.
Key to remember here is:
- first sort
- then limit
Not the other way around, ROWNUM is retrieved before any sort is applied. This is the reason why I wrap it in an inner select.
This is in the FAQ: How does one select the TOP N rows from a table?
And if you'd search for "top N" you probably get quite some hits too.
MHE
[Updated on: Tue, 11 December 2007 06:32] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Rownum problem? [message #290454 is a reply to message #287184] |
Sat, 29 December 2007 00:40   |
tondapi
Messages: 99 Registered: August 2007 Location: usa
|
Member |
|
|
In my table we have 100 rows but user want to see last 20 rows.
I use rownum but ia m not able to get that one.Any one help me plz.
|
|
|
|
Re: Rownum problem? [message #290456 is a reply to message #290454] |
Sat, 29 December 2007 00:47   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Locked.
I will not even merge this with your other posts.
Continue in the other thread and stop reposting the same question over and over.
|
|
|
|
Re: nth row, last 10 rows, first 25 rows, rownum (merged 4 topics) [message #290635 is a reply to message #287184] |
Sun, 30 December 2007 23:16   |
spmano1983
Messages: 269 Registered: September 2007
|
Senior Member |
|
|
Hi,
You can do same rownum
select ename,deptno,sal from (select rownum,ename,deptno,sal from emp order by rownum desc) where rownum <=20;
Kindly revert back.
Thanks
Mano
|
|
|
|
|
|