Hi,
I am looking for a query which selects the maximum value of sequence number for various file id Prefix.
Here is the query which I have written but I its not working. Could some one please help me in resolving this issue?
SELECT A.FILE_TYPE
,A.FILE_ID_SUFFIX
,A.RUN_DATE_TIME
,A.FILE_SEQ_NUMBER
,A.FILE_DESCRIPTION
,A.TRANSFER_DATE_TIME
,A.TRANSFR_RESULT_IND
A.RECORD_COUNTER
FROM DPAXGA3.TAX523 A
INNER JOIN
( SELECT MAX(FILE_SEQ_NUMBER) MAX_SEQ_NUM, FILE_ID_SUFFIX
FROM DPAXGA3.TAX523
WHERE FILE_TYPE = 'SHEC'
AND FILE_ID_SUFFIX IN ('030','004','025')
GROUP BY FILE_ID_SUFFIX
) B ON A.FILE_TYPE = 'SHEC'
AND A.FILE_ID_SUFFIX = B.FILE_ID_SUFFIX
AND A.FILE_SEQ_NUMBER = B.MAX_SEQ_NUM
Not exactly the best description. What's the problem ?
Syntax error ?
no results ?
wrong results ?
time-out or locking problem ?
but anyhow : the psychic force is strong today and here's A solution for AN issue.
Code:
SELECT A.FILE_TYPE
,A.FILE_ID_SUFFIX
,A.RUN_DATE_TIME
,A.FILE_SEQ_NUMBER
,A.FILE_DESCRIPTION
,A.TRANSFER_DATE_TIME
,A.TRANSFR_RESULT_IND
,A.RECORD_COUNTER
FROM DPAXGA3.TAX523 A
WHERE A.FILE_TYPE = 'SHEC'
AND A.FILE_ID_SUFFIX IN ('030','004','025')
AND A.FILE_SEQ_NUMBER = ( SELECT MAX(FILE_SEQ_NUMBER)
FROM DPAXGA3.TAX523 B
WHERE B.FILE_TYPE = A.FILE-TYPE
AND B.FILE_ID_SUFFIX = A.FILE_ID_SUFFIX)
