View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
sskpen,
You may be looking for something like this,
Code: |
SELECT *
FROM BOBT1.EMPLOYEE
ORDER BY FIRSTNME DESC
FETCH FIRST 1 ROW ONLY
;
|
Thanks,
Sushanth |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
there is no such a thing as a last record in a db2 table
the <things> are called rows |
And there is no such thing as a "last row". Rows are rows. . . Using an ORDER BY and ascending or descending a query might return the highest or lowest value (s) from the found set, but these are neither "first" nor "last".
First and last are for sequential data - that has a beginning and an end. |
|
Back to top |
|
|
sskpen
New User
Joined: 30 Aug 2006 Posts: 14
|
|
|
|
Thank you all for your responses |
|
Back to top |
|
|
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.
Code: |
Select * from Table1 where Key = (Select Max(Key) from Table1); |
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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". |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
bompally.praveen wrote: |
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. |
Again,
Records have nothing to do with DB2.
And to continually insist that you are correct is fruitless.
just because a vsam keyed file, which has records,
and keyed files are usually written in an ascending sequence,
and we all know that the underlying file structure for db2 on a z/OS mainframe is a linear dataset,
does not mean that there is a concept of records and db2
does not mean there is a concept of first and last without an order by
if the row with the highest (max) unique primary key
was the first inserted into the db2 table,
and the db2 table has not been reorged,
then there is as good a chance as any that the supporting linear vsam record that contains the value for the requested row,
is the first.
which means that this
bompally.praveen wrote: |
It won’t work on other columns but it works with key on ascending order. The key must be primary key. |
is bs. |
|
Back to top |
|
|
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] |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
Quote: |
At end of the day, I am learning some thing from the forum |
looks like not
stop arguing and posting erroneous info
re-read the whole topic and try to understand what is being said |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6248 Location: Mumbai, India
|
|
|
|
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
I got the last row on every execution |
No, you did not. . . You could not because there is no "last row".
You might have gotten the result you wanted (the highest value), but insisting on calling it the "last row" when it has been clearly explained that no such thing exists is not a good thing. . .
Using proper terminology is as important as getting the answer you want. Otherwise, later, there will be other mistakes due to misunderstanding.
Keep learning |
|
Back to top |
|
|
|