CASE WHEN (FIELD1 = 1 AND FIELD2 = 2) THEN SELECT THE MAX OF THIS RECORD FROM THIS FIELD

[rodrunner79]

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.

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.

 

[rodrunner79]

Sorry, wrong query. Here's the right query:

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 = B.VERS_NO

Can't means won't try.

 

[imex]

Hi,

Try:

with CTE as
(
    select t.*, ROW_NUMBER() OVER(PARTITION BY t.REC_ID order by t.VERS_NO desc) as RowNum
    from Table1 as t
    where (t.F_CD > 1) and (t.P_CD = 3)
)

select * from CTE where RowNum = 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[rodrunner79]

Hi imex,

First, thanks for your response. I tried your suggestion and the query returned only records where (t.F_CD > 1) and (t.P_CD = 3). Your query is only returning Row 2 and 5. That's not what I'm expecting but that's my fault for not properly articulating what I expect the results to return. I think what's missing is an ELSE (F_CD < 1).

Here's what I need it to do, if you look at the data, row 6 & 7 are both version 1 with a their own REC_ID. I need the query to return these records when F_CD < 1 and P_CD = 3 but also return only the rows with the highest maximum version where F_CD > 1 and P_CD = 3. Basically from a business operation, F_CD is my indicator whether the document record is new or has been modified. If F_CD is 1, it's new and if it's greater than 1, then it has been modified; therefore, I need to return the highest version for that ID only where F_CD does not equal 1. So given that, if I was to run the query, I would expect it to only return the following records:

REC_ID F_CD P_CD S_CD VERS_NO
A 2 3 4 3
B 2 3 4 2
C 1 3 4 1
D 1 3 4 1


Can't means won't try.

 

[rodrunner79]

Why can't you edit these post.... Let me rephrase my last sentences:

Basically from a business operation standpoint, F_CD would be my indicator of whether the document record is new or has been modified. If F_CD is 1, it's new and if it's greater than 1, then it has been modified; therefore, I need to return the highest version ONLY for that ID where F_CD does not equal 1.

Can't means won't try.

 

[imex]

Try:

with CTE as
(
    select t.*, ROW_NUMBER() OVER(PARTITION BY t.REC_ID order by t.VERS_NO desc) as RowNum
    from Table1 as t
    where (t.P_CD = 3)
)

select * from CTE where RowNum = 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[rodrunner79]

Thank you! That did it.

If you don't mind, could you explain to me what this query is doing. I know it's a common table expression or temp table but never really used one. I just need to understand it.

Can't means won't try.

 

[imex]

See the pages below:

http://msdn.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Hope this helps.


[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]