Case When Conditions - SQL Server Forums

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources