Query does not yield correct (highest) sequence number -

[karlomutschler]

Hi,
this query should only deliver the record with the highest sequence number, but does not.

SELECT *
FROM con_status_history csh1
INNER JOIN con_status_history csh2 ON csh1.contract_id = csh2.contract_id
AND (csh2.contract_id, csh2.seqno) IN
(SELECT csh3.contract_id, max(csh3.seqno)
FROM con_status_history csh3
GROUP BY csh3.contract_id)
WHERE csh1.contract_id = 2117431

Result:

CONTRACT_ID SEQNO REQUEST_DATE
2117431 4 2001-06-15
2117431 3 2001-06-06
2117431 2 2001-03-30
2117431 1 2001-03-24

TIA
Kind regards
Karlo

 

[vtj]

Try this

SELECT CHS1.CONTRACT_ID, CSH1.SEQNO, CSH1.REQUEST_DATE
FROM CON_STATUS_HISTORY CHS1
WHERE CHS1.SEQNO =
(SELECT MAX(CHS2.SEQNO) FROM CON_STATUS_HISTORY CHS2
WHERE CHS1.CONTRACT_ID CHS2.CONTRACT_ID
GROUP BY CHS2.CONTRACT_ID)

This works on an AS/400 using interactive SQL. I am assuming all your result fields are in CON_STATUS_HISTORY.

Good Luck VTJ