sql server 2008 - SQL query select row when match is made, select other row when match is not made -


i have 2 tables:

attribuut

attribuutvalue

they have one-to-many relationship. attribuut can have multiple attribuutvalues. these attribuutvalues contain states.

now want query gives me latest attribuutvalue attribuut, has: state 3 or state 6.

then hit problem: when attribuut contains attribuutvalue state 4, latest attribuutvalue state 3 should shown.

select distinct * attribuut att left join attribuutvalue value on (value.attribuuthead = att.displayid) value.status = 3 or (value.status = 6  , not exists      (select * attribuutvalue value2 value2.valueid = value.valueid , value2.status = 4))  order valueid desc 

however gives me not resultset want. there still attribuutvalues state 4 shown. , doesnt give me last record in list...

first problem have in not exists sub-query. should have joined on attribuuthead , not valueid (which presume unique key on table)

second missing mechanism filter 1 value per attribuuthead. row_number() can achieve that.

so fixed query this:

select *  (     select *, row_number() on (partition attribuuthead order valueid desc) rn     dbo.attribuutvalue v     status = 3 or (status = 6 , not exists (select * attribuutvalue v2 v2.attribuuthead = v.attribuuthead , v2.status = 4)) ) x inner join attribuut on x.attribuuthead = a.displayid x.rn = 1 

sqlfiddle demo


Comments

Popular posts from this blog

php - Calling a template part from a post -

Firefox SVG shape not printing when it has stroke -

How to mention the localhost in android -