MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Select row with Maximum sequence value

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Piscesian
Beginner


Joined: 17 Dec 2004
Posts: 14
Topics: 8
Location: Monrovia,California

PostPosted: Sun Dec 02, 2007 12:51 am    Post subject: Select row with Maximum sequence value Reply with quote

Hi,
I have a DB2 table in which rows are stored in the following manner
Code:

SDNum   SeqNum   Textdata
84510   4   dddd
84510   3   cccc
84510   2   bbbb
84510   1   aaaa
84511   3   cccc
84511   2   bbbb
84511   1   aaaa
84512   2   yyyy
84512   1   xxxx

I need all the SDNum rows that have maximum SeqNum value
Considering the above example, output of SQL should be
Code:

84510   4   dddd
84511   3   cccc
84512   2   yyyy

Can anyone suggest how to achieve this?

Thanks,
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose

PostPosted: Sun Dec 02, 2007 3:11 am    Post subject: Reply with quote

Piscesian,

Try this untested sql

Code:

SELECT *
  FROM TABLE
 WHERE (SDNUM,SEQNUM) IN (SELECT SDNUM
                                ,MAX(SEQNUM)
                            FROM TABLE
                           GROUP BY SDNUM)
;


Hope this helps...

Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vivek1983
Intermediate


Joined: 20 Apr 2006
Posts: 222
Topics: 24

PostPosted: Sun Dec 02, 2007 11:17 pm    Post subject: Reply with quote

Piscesian,

I guess the following will work also:

Code:
 

  SELECT SDNUM, MAX(SEQNUM) AS SEQNUM, TEXTDATA FROM TABLE GROUP BY SDNUM

 

_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay)
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose

PostPosted: Mon Dec 03, 2007 11:13 am    Post subject: Reply with quote

vivek1983 wrote:
Piscesian,

I guess the following will work also:

Code:
 

  SELECT SDNUM, MAX(SEQNUM) AS SEQNUM, TEXTDATA FROM TABLE GROUP BY SDNUM

 


Vivek,

Please TEST before you post. Your SQL will NOT work as the grouping is inconsistent. You should have a group by clause on both SDNUM and Textdata

Hope this helps...

Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group