DBV - SQL Query to exclude max Sequence Numbers



IBM's flagship relational database management system

DBV - SQL Query to exclude max Sequence Numbers

Postby Andyf » Wed Jun 30, 2010 8:32 pm

I have a DB2 SQL query that returns groups of rows that have a sequence number within the data. I want to change the SQL query so that each row from the group with the maximum sequence number is excluded.

For Example in the following results I want to exclude the rows marked <======= from the final results
ACCOUNT_ID  CONTRACT_ID  POSTING_SEQ_NUM  RNL_CMSN_STRT
---------+---------+---------+---------+---------+---------+---------+--------
10084011   SP93039471           1         01.08.2009         
10084011   SP93039471           2         01.08.2009         
10084011   SP93039471           3         01.08.2009        <======
10084011   SP93039471           1         01.09.2009         
10084011   SP93039471           2         01.09.2009        <======
10084011   SP93039471           1         01.10.2009         
10084011   SP93039471           2         01.10.2009        <======


Note that ACCOUNT_ID and CONTRACT_ID do change further down the list of output results and there are other fields in the output results but they are not key fields, the only key fields are ACCOUNT_ID, CONTRACT_ID and RNL_CMSN_STRT
Andyf
 
Posts: 10
Joined: Mon May 24, 2010 3:16 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DBV - SQL Query to exclude max Sequence Numbers

Postby GuyC » Thu Jul 01, 2010 6:33 pm

from TAB1 A
where A.POSTING_SEQ_NUM  < (select max(POSTING_SEQ_NUM ) from tab1 B
                             where A.ACCOUNT_ID  = B.ACCOUNT_ID
                               and A.CONTRACT_ID = B.CONTRACT_ID )
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post