Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fetch Last Record in DB2 Table using SQL Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sskpen

New User


Joined: 30 Aug 2006
Posts: 14

PostPosted: Tue Jun 08, 2010 11:08 am    Post subject: Fetch Last Record in DB2 Table using SQL Query
Reply with quote

Hi All

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

Thanks in anticipation
Sreekanth
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Tue Jun 08, 2010 11:14 am    Post subject: Reply to: Fetch Last Record in DB2 Table using SQL Query
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Jun 08, 2010 7:15 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jun 08, 2010 7:23 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sskpen

New User


Joined: 30 Aug 2006
Posts: 14

PostPosted: Wed Jun 09, 2010 5:19 pm    Post subject: Reply to: Fetch Last Record in DB2 Table using SQL Query
Reply with quote

Thank you all for your responses
Back to top
View user's profile Send private message
PKB

New User


Joined: 06 Jul 2008
Posts: 10
Location: hyd

PostPosted: Sun Jun 13, 2010 5:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Jun 13, 2010 10:20 pm    Post subject:
Reply with quote

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
View user's profile Send private message
PKB

New User


Joined: 06 Jul 2008
Posts: 10
Location: hyd

PostPosted: Sun Jun 13, 2010 10:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Jun 13, 2010 11:21 pm    Post subject:
Reply with quote

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
View user's profile Send private message
PKB

New User


Joined: 06 Jul 2008
Posts: 10
Location: hyd

PostPosted: Mon Jun 14, 2010 11:29 am    Post subject:
Reply with quote

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. icon_biggrin.gif

Thanks,
Praveen Bompally
[/quote]
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Mon Jun 14, 2010 11:33 am    Post subject: Reply to: Fetch Last Record in DB2 Table using SQL Query
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Jun 14, 2010 2:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jun 14, 2010 7:39 pm    Post subject:
Reply with quote

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 icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us