OraFAQ Forum: SQL & PL/SQL » How to get last 10 rows from table

Home » SQL & PL/SQL » SQL & PL/SQL » How to get last 10 rows from table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get last 10 rows from table [message #8842]

Mon, 29 September 2003 23:33

rajesh
Messages: 173
Registered: November 1998

Senior Member

Hi All
Please let me know how to get last 10 records from a table.

I know one method ... is there any other way

select * from table
MINUS
select * from table where rownum <((select count(*)-9 from table) )

Thanks in advance

Regards
Rajesh

Report message to a moderator

Re: How to get last 10 rows from table [message #8843 is a reply to message #8842]

Tue, 30 September 2003 00:28

Maaher
Messages: 7065
Registered: December 2001

Senior Member

And what is your criterium for "last"? Tables aren't sorted in Oracle. You cannot guarantee the output. After some DML the row that appeared as "first" five minutes ago, might turn up as "last" now. So without any form of order by, you might as well use "where rownum < 11" to limit the output of the select. Trust me on this one, it is a plain and simple fact that you need at least an order by statement. Once you get that, you can reverse the order and limit the output either by using the rownum solution or using rank() or dense_rank(), which has the advantage you can also take any particular subset (like row 20 to 31)... . Here's an example that'll select the rows 5 to 7 of the EMP table sorted by salary (SAL) and ename (ENAME), the latter to make sure that the sort is unique:

SQL> SELECT ename, sal
  2    FROM emp e
  3   ORDER BY sal DESC;

ENAME            SAL
---------- ---------
KING            5000 
SCOTT           3000
FORD            3000
JONES           2975
BLAKE           2850 -- row we're interested in
CLARK           2450 -- row we're interested in
ALLEN           1600 -- row we're interested in
TURNER          1500
MILLER          1300
WARD            1250
MARTIN          1250
ADAMS           1100
JAMES            950
SMITH            800

14 rows selected.

SQL> SELECT x.ename
  2       , x.sal
  3       , x.r
  4    FROM ( SELECT e.ename
  5                , e.sal
  6                , rank() OVER ( ORDER BY sal DESC, ename ASC ) r
  7             FROM emp e
  8         ) x
  9   WHERE x.r BETWEEN 5 AND 7
 10  /

ENAME            SAL         R
---------- --------- ---------
BLAKE           2850         5
CLARK           2450         6
ALLEN           1600         7

SQL>

And that's the beauty of it: to get the last rows of a sorted table, simply reverse the order and limit the output.

MHE

Report message to a moderator

Re: How to get last 10 rows from table [message #8846 is a reply to message #8843]

Tue, 30 September 2003 00:58

rajesh
Messages: 173
Registered: November 1998

Senior Member

Thanks maaher. I want last 10 records ... let us say i sorted on one column in the table ...

Report message to a moderator

Re: How to get last 10 rows from table [message #8847 is a reply to message #8846]

Tue, 30 September 2003 01:35

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Use the 'reverse order technique'. Let's say you want the last 10 records of this query:

SQL> SELECT ename
  2       , sal
  3       , r
  4    FROM ( select e.ename
  5                , e.sal
  6                , rank() OVER ( ORDER BY sal DESC ) r
  7             from emp e
  8*        )
SQL> /

ENAME            SAL         R
---------- --------- ---------
KING            5000         1
SCOTT           3000         2
FORD            3000         2
JONES           2975         4
BLAKE           2850         5
CLARK           2450         6
ALLEN           1600         7
TURNER          1500         8
MILLER          1300         9
WARD            1250        10
MARTIN          1250        10
ADAMS           1100        12
JAMES            950        13
SMITH            800        14

14 rows selected.

Then you would do this:

SQL> SELECT ename
  2       , sal
  3       , r
  4    FROM ( select e.ename
  5                , e.sal
  6                , rank() OVER ( ORDER BY sal <FONT COLOR='RED'><B>ASC</B></FONT> ) r
  7             from emp e
  8         )
  9*  WHERE r < 11
SQL> /

ENAME            SAL         R
---------- --------- ---------
SMITH            800         1
JAMES            950         2
ADAMS           1100         3
WARD            1250         4
MARTIN          1250         4
MILLER          1300         6
TURNER          1500         7
ALLEN           1600         8
CLARK           2450         9
BLAKE           2850        10

10 rows selected.

SQL>

Report message to a moderator

Re: How to get last 10 rows from table [message #8852 is a reply to message #8842]

Tue, 30 September 2003 06:05

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Agree with Maaher.Unless you sort the rows explicitly,you are not guareented to get the rows in any predictable order. Pls see my post earlier regarding Rowids and row ordering.

-Thiru

http://www.orafaq.net/msgboard/newbies/messages/10577.htm

Report message to a moderator

Previous Topic:	what is the use of wrap?
Next Topic:	how to insert data from a long data type column to varchar type column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 17:46:01 CDT 2024