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
 

 

I want the last record of the perticular set of rows.

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

New User


Joined: 21 Nov 2007
Posts: 8
Location: Bangalore

PostPosted: Mon Sep 14, 2009 12:50 pm    Post subject: I want the last record of the perticular set of rows.
Reply with quote

I have to search a set of rows with a particular rule.

Eg: rows whose name = xyz.

Then I will get some 5 rows.

Id name
2 xyz
3 xyz
4 xyz
5 xyz

now I want to fetch only the last record.

that is

Id name
5 xyz

Id is a varchar


Please provide me the one single query to execute this.

thnaks in advance
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10279
Location: italy

PostPosted: Mon Sep 14, 2009 12:55 pm    Post subject: Reply to: I want the last record of the perticular set of ro
Reply with quote

did You sarch the forums, looks like not,
if You had you would have found many topics dicussing the issue
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon Sep 14, 2009 1:31 pm    Post subject:
Reply with quote

Code:
select Id name from tablename order by Id desc fetch first 1 rows only
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Mon Sep 14, 2009 1:41 pm    Post subject:
Reply with quote

Hi Sambhaji,

i have a question. If you use ORDER BY caluse will that won't change the order and gives a differet output (than what expected).
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon Sep 14, 2009 1:44 pm    Post subject:
Reply with quote

Quote:
i have a question. If you use ORDER BY caluse will that won't change the order and gives a differet output (than what expected).

Thats what we wanted here if I have understood the requirement..

Can you explain with example what you wanted to say?
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Mon Sep 14, 2009 1:55 pm    Post subject:
Reply with quote

The input provided is in the sorted order. But in real time the SELECT statement may or maynot be in the sorted order. So if the SELECT stmt is not in the sorted order the last record won't be the same as when you provide
Quote:

order by Id desc fetch first 1 rows only
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Sep 14, 2009 2:00 pm    Post subject:
Reply with quote

arvind.m wrote:
Hi Sambhaji,

i have a question. If you use ORDER BY caluse will that won't change the order and gives a differet output (than what expected).



If you do not use order by, you don't know what to expect. DB2 does not retrive the rows in any particular order unless you specify an ORDER BY.
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 Sep 14, 2009 4:19 pm    Post subject:
Reply with quote

arvind.m wrote:
If you use ORDER BY caluse will that won't change the order and gives a differet output (than what expected).
Without an ONDER BY You just arbitrarily selected a row. It could be different the next time you do it, or it could be the same for the life of the table. Again, this is a complete waste of a query without an ORDER BY clause.
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Mon Sep 14, 2009 5:01 pm    Post subject:
Reply with quote

Use this query, if you want:

Code:
select * from table1
where id = (select max(id) from table1)
fetch first row only


Lenny
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10279
Location: italy

PostPosted: Mon Sep 14, 2009 5:10 pm    Post subject: Reply to: I want the last record of the perticular set of ro
Reply with quote

the query would be right if the topic starter had specified a reasonable rule...

asking for the last one without any concern for the order,max, min or any other sql compliant constraint is just poor understanding of how sql works
Back to top
View user's profile Send private message
arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Mon Sep 14, 2009 6:38 pm    Post subject:
Reply with quote

Yes. i agree with enrico.
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
No new posts Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 6 Wed Jul 26, 2017 7:35 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts Need to write record of PS File in ex... Chandan1993 JCL & VSAM 1 Wed Jun 07, 2017 1:35 am
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm


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