Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need to retrieve the row with max SEQ_NR

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

New User


Joined: 01 Feb 2007
Posts: 30
Location: Mumbai

PostPosted: Thu Jan 24, 2008 1:02 pm    Post subject: Need to retrieve the row with max SEQ_NR
Reply with quote

i have the following table structure

ACCT_ID SEQ_NR
81256382 9999
81256382 1
81256382 3
79350011 9999
79350011 1
79350011 2

for each of the account id's I need to retrieve the row with max SEQ_NR
but row with SEQ_NR 9999 should not be retrieved.


PLease help me with the SQL query
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Thu Jan 24, 2008 1:07 pm    Post subject:
Reply with quote

Tushar,

Customize to your need.

http://www.ibmmainframes.com/viewtopic.php?t=1472&highlight=maximum
Back to top
View user's profile Send private message
tushar_study

New User


Joined: 01 Feb 2007
Posts: 30
Location: Mumbai

PostPosted: Thu Jan 24, 2008 1:18 pm    Post subject: Reply to: please help --- sql query
Reply with quote

Hi MMohan,

thanks for the reply ...actually i m looking for sql using JOINS...can u give me an idea that if i dont wanna include 9999 then can i give SEQ_NR <> 9999 in the where clause of the join


Thanks and Regards,

Tushar
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Thu Jan 24, 2008 3:19 pm    Post subject:
Reply with quote

Hi tushar,
i ve not executed this query however, you may check it out :

SELECT ACCT_ID, SEQ_NR FROM MYTABLE A WHERE SEQ_NR = (SELECT MAX(SEQ_NR) FROM MYTABLE B WHERE SEQ_NR <> 9999 AND A.SEQ_NR=B.SEQ_NR);
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Jan 24, 2008 6:41 pm    Post subject:
Reply with quote

May need some tweeking.


Code:

--This grabs the max regardless of 9999 or not
SELECT acct_id, max(seq_nr) as max_nr
 FROM table
 GROUP BY acct_id

--Try this from a performance standpoint, I don't know if it will work
SELECT acct_id, max(seq_nr) as max_nr
 FROM table
 WHERE seq_nr <> 9999
 GROUP BY acct_id

--This should work but may be bad for performance
SELECT A.acct_id, max(A.seq_nr)
FROM
 (SELECT *
  FROM table
  WHERE seq_nr <> 9999) A
GROUP BY A.acct_id

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 Need to retrieve Julian_date Suganya87 DB2 4 Wed Aug 17, 2016 7:27 pm
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm
No new posts retrieve line commands from the data ... Pedro TSO/ISPF 6 Sat Oct 17, 2015 5:41 am
No new posts DFSORT - Retrieve selective records f... narasimha_devi DFSORT/ICETOOL 8 Thu Aug 06, 2015 4:12 pm
No new posts Retrieve records in the order of alte... jacobdng Compuware & Other Tools 8 Fri Jul 31, 2015 8:16 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us