rodrunner79
IS-IT--Management
Hi there, I've been trying to find out how to do this but I can't get the right results so I am resorting to tek-tips to help me out, as I've always had positive experience in these forums.
Just to preface the problem, here's my table of data:
REC_ID F_CD P_CD S_CD VERS_NO
A 1 5 4 1
A 2 3 4 3
A 2 1 2 2
B 1 5 4 1
B 2 3 4 2
C 1 3 4 1
D 1 3 4 1
What I want the query to return is all IDs where IF the F_CD > 1 and the P_CD = 3, then find the maximum VERS_NO and return that record only . So if you look at the first 3 records, it's the same ID but there's 3 versions, I only want to return 1 record out of the three because only 1 record have both F_CD greater than 1 and P_CD equals 3.
Here's what I got but it's returning all the records still.
Can't means won't try.
Just to preface the problem, here's my table of data:
REC_ID F_CD P_CD S_CD VERS_NO
A 1 5 4 1
A 2 3 4 3
A 2 1 2 2
B 1 5 4 1
B 2 3 4 2
C 1 3 4 1
D 1 3 4 1
What I want the query to return is all IDs where IF the F_CD > 1 and the P_CD = 3, then find the maximum VERS_NO and return that record only . So if you look at the first 3 records, it's the same ID but there's 3 versions, I only want to return 1 record out of the three because only 1 record have both F_CD greater than 1 and P_CD equals 3.
Here's what I got but it's returning all the records still.
SQL:
SELECT A.REC_ID, A.VERS_NO, A.F_CD, A.P_CD, A.S_CD
FROM Table1 A,
(SELECT CASE WHEN (F_CD > 1 AND P_CD = 3) THEN MAX(VERS_NO) ELSE 0 END AS MAX_VERS_NO, REC_ID, VERS_NO
FROM Table1
GROUP BY REC_ID, DOC_VERS_NO, F_CD, S_CD) B
WHERE A.REC_ID = B.REC_ID AND
A.VERS_NO = H2.DOC_CD AND
H.REC_ID = H2.REC_ID AND
H.VERS_NO = H2.VERS_NO
Can't means won't try.