IBM Mainframe Forum

by **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

Select all

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

by **GuyC** » Thu Jul 01, 2010 6:33 pm

Select all

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 )

IBM Mainframe Forum

DBV - SQL Query to exclude max Sequence Numbers

DBV - SQL Query to exclude max Sequence Numbers

Re: DBV - SQL Query to exclude max Sequence Numbers