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
Comments
Post a Comment