Page 1 of 1

DBV - SQL Query to exclude max Sequence Numbers

PostPosted: Wed Jun 30, 2010 8:32 pm
by Andyf
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

Re: DBV - SQL Query to exclude max Sequence Numbers

PostPosted: Thu Jul 01, 2010 6:33 pm
by GuyC
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 )