DBV - SQL Query to exclude max Sequence Numbers
Posted: 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
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
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 <======
---------+---------+---------+---------+---------+---------+---------+--------
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