Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Case When Conditions

Author  Topic 

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-10-14 : 10:17:11
Hi,

today I struggle a bit with my Case When condition and hope you can help me. I have the following:


MAX(Vernum) AS test,
MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 THEN A2.ValStr END) AS TYPE_DOCUMENT


This works not perfect. In many cases I have more then one row and my query takes the max value of column Valstr. Thats is not exactly what I want. I'd like to have the value of Column Valstr of the row where column Vernum has the maximum value.

I've tried many things like:



MAX(Vernum) AS test,
MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 AND A2.Vernum=test THEN A2.ValStr END) AS TYPE_DOCUMENT




OR


MAX(Vernum) AS test,
MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 AND A2.Vernum=MAX(A2.Vernum) THEN A2.ValStr END) AS TYPE_DOCUMENT[/code]


I hope you understand what I mean.:) How can I get the value of one column and have the maximum condition of another column?

Kind regards,

Lara

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-14 : 10:33:04
You probably need a subquery


select my.valstr
from mytable my
join (select valstr, max(vernum)
from @t
group by valstr
) sub(valstr, max_vernum)
on my.valstr = sub.valstr


Note that, since you did not post your whole query, it's not possible to put this logic in context.
Go to Top of Page
   

- Advertisement -