Fetch Last Record in DB2 Table using SQL Query

sskpen · New User Joined: 30 Aug 2006 Posts: 14

Hi All

Could someone suggest how to retrieve last record in DB2 Table using SQL Query

Thanks in anticipation
Sreekanth

enrico-sorichetti · Posted: Tue Jun 08, 2010 11:14 am

there is no such a thing as a last record in a db2 table
the <things> are called rows anyway

do not rely on the rows being returned in any particular sequence unless You specify an order by clause

then to an ordered set You cam apply the concept of ..
first ==> the row with the lowest key as specified by the order clause
last ==> the row with the highest key
obviously the concept low/high is reversed is the order is descending

sushanth bobby · Posted: Tue Jun 08, 2010 7:15 pm

sskpen,

You may be looking for something like this,

dick scherrer · Posted: Tue Jun 08, 2010 7:23 pm

Hello,

sskpen · New User Joined: 30 Aug 2006 Posts: 14

Thank you all for your responses

PKB · New User Joined: 06 Jul 2008 Posts: 10 Location: hyd

If you have a key in the table then you can get the last record with MAX(Key) in where condition. hope the below query may help you.

dick scherrer · Posted: Sun Jun 13, 2010 10:20 pm

Hello,

No, you can't. . . There is NO last record. . .

There is a row with MAX value for the specified predicate, but this is not the "last record".

PKB · New User Joined: 06 Jul 2008 Posts: 10 Location: hyd

It won’t work on other columns but it works with key on ascending order. The key must be primary key.

Thanks,
Praveen Bompally.

dbzTHEdinosauer · Posted: Sun Jun 13, 2010 11:21 pm

PKB · New User Joined: 06 Jul 2008 Posts: 10 Location: hyd

Dick,
Before I gave the answer, I tried that query on some tables. I got the last row on every execution (I note down the last row when I fetched all the rows, then I executed my query and noticed that It was same row as last row in prior execution). So I gave my practical results. That’s what I am little confident on my query.

As you said ‘if the table is not reorged’, then I will be in trouble.

If Sreekanth looking for last inserted row, then my query never gives expected results.

At end of the day, I am learning some thing from the forum.

Thanks,
Praveen Bompally
[/quote]

enrico-sorichetti · Posted: Mon Jun 14, 2010 11:33 am

Anuj Dhawan · Posted: Mon Jun 14, 2010 2:35 pm

Though the answers from Dick adn Enrico pretty much explain everything about how the question should have been asked? What's wrong with the terminologies used and also a bit more...? However for my $.02 ...

The last rows should be based on the order of some column as had been stated earlier too; however, if you would have asked - how to select the last row of the resultset , most probably confusuion could have gone away from the first post itself.

But, as they say, the golden rule of SQL: Tell what you want. Make no assumption on how the RDBMS will get it. Keeping that in mind, FETCH FIRST 1 ROWS does NOT fetch the first row (record ?) from the table UNLESS you have added a clustered index to the table and you have included an order by clause to your select statement that causes the optimizer to select the clustered index in its plan. Well, having said that one can beat me to on that and to save myself i'd say, technically, this is only guaranteed to be true if the select statement is run immediately after the clustered index is created or a table reorg is performed, (as Dick has suggested) and before the next table insert is performed.

As we talked about underlined LDSes also, you have to keep in mind that the physical storage doesn't have to be aligned with the order of the insert of the rows. And the whole point of relational database systems is that the user should not worry how data is physically stored. In short, the whole concept of "first row" is pretty much meaningless in SQL without any order being specified, and the only way for that is to specify an ORDER BY - full stop.

dick scherrer · Posted: Mon Jun 14, 2010 7:39 pm

Hello,