How to get the last 10 records from a table



IBM's flagship relational database management system

How to get the last 10 records from a table

Postby anupam » Thu Dec 27, 2007 10:39 am

How to get last 10 records from a table using SQL query?
anupam
 
Posts: 2
Joined: Wed Dec 26, 2007 3:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to get the last 10 records from a table

 

Re: How to get the last 10 records from a table

Postby the_gautam » Thu Jan 03, 2008 11:45 am

select * from <table-name> A where 10 > ( select count (*) from <table-name> B where A.<column-name> = B.<column-name> );
the_gautam
 
Posts: 2
Joined: Thu Jan 03, 2008 11:39 am
Has thanked: 0 time
Been thanked: 0 time

Re: How to get the last 10 records from a table

Postby ronmathew » Mon Jan 14, 2008 4:34 pm

the_gautam wrote:select * from <table-name> A where 10 > ( select count (*) from <table-name> B where A.<column-name> = B.<column-name> );


I cannot understand ur solution...

Do v get the 10 record with this...and it has to be the last records....plz explain!!
ronmathew
 
Posts: 6
Joined: Mon Jan 14, 2008 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to get the last 10 records from a table

Postby dick scherrer » Wed Jan 16, 2008 1:59 am

Hello,

There is no such thing as the "last 10 rows" in a table.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: How to get the last 10 records from a table

Postby ronmathew » Wed Jan 16, 2008 5:30 pm

dick scherrer wrote:Hello,

There is no such thing as the "last 10 rows" in a table.


SO what do we say...no last 10 rows...10 records in a table
ronmathew
 
Posts: 6
Joined: Mon Jan 14, 2008 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to get the last 10 records from a table

Postby arunprasad.k » Wed Jan 16, 2008 6:07 pm

Use FETCH FIRST and ORDER BY (DESC) to get the last n records.

Refer the URL:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/4.4.7?SHELF=&DT=20010718164132

Especially the last paragraph.

Arun.
arunprasad.k
 
Posts: 110
Joined: Thu Dec 27, 2007 5:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to get the last 10 records from a table

Postby dick scherrer » Thu Jan 17, 2008 12:42 am

Hello,

SO what do we say...no last 10 rows...10 records in a table
We should not use that kind of terminology. First/last are sequential file concepts. When teachers or text material use this sort of reference for database activity, they are mis-leading the students.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: How to get the last 10 records from a table

Postby edward.lan » Tue Apr 01, 2008 7:27 pm

hi, try this

select * from TABLE
order by KEY DESC
fetch first 10 rows only

edward
edward.lan
 
Posts: 1
Joined: Sat Jan 19, 2008 12:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: How to get the last 10 records from a table

Postby dick scherrer » Tue Apr 01, 2008 9:40 pm

Hello edward.lan and welcome to the forums,

Please understand that the "solution" you posted does not "get the last 10" in the table - as mentioned before, there is no last 10 (nor is there a first 10). . . Databases to not have a front and a back row or a beginning and an end row.

Your query should return the 10 rows with the highest KEY, but that does not make them the "last 10".
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: How to get the last 10 records from a table

Postby progressabhi1 » Wed Apr 30, 2008 5:11 pm

SELECT * FROM <table-name>
WHERE SEQ_NO >
(SELECT MAX(SEQ_NO)-20 FROM <table-name> );
====
note:-where SEQ_NO must be a column(attribute) in the table
*************************************************************************************************************
SELECT * FROM <table-name>
ORDER BY SEQ_NO DESC
FETCH FIRST 20 ROWS ONLY;
====
This one is just in accordance to the previous query,but with arrangment done in descending order.
*************************************************************************************************************
progressabhi1
 
Posts: 17
Joined: Tue Apr 01, 2008 6:07 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post