IBM Mainframe Forum

How to get last 10 records from a table using SQL query?

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

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

Hello,

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

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

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.

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.

hi, try this

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

edward

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

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