sql - Select one distinct row based on a case statement applied to a column -

sql - Select one distinct row based on a case statement applied to a column -

i'm unable figure out sql query (using ms sql server). i'm trying retrieve single row dataset in item 1 id can have more 1 row. part throwing me off right row should based on "hierarchy". have trying throw case statement @ problem.

some sample data:

id class date 100 reddish 2012-12-12 100 bluish 2012-12-31 200 reddish 2012-10-31 300 greenish 2012-04-04 300 bluish 2011-09-01

i want homecoming single row based on value of class.

case when reddish date case when bluish date case when greenish date else ''

my final dataset should this:

id class date 100 reddish 2012-12-12 200 reddish 2012-10-31 300 bluish 2011-09-01

so, if 1 of duplicate rows has value of red, utilize date row first. blue, green.

been working on 1 day, playing around subqueries, grouping bys, havings, case statements, derived tables. i'm quite rusty on sql skills, it's been while.

any hints on direction should take?

you can seek this

;with cte ( select id, class, date, row_number() on (partition id order case class when 'red' 1 when 'blue' 2 when 'green' 3 else 4 end) rn mytable ) select id, class, date cte rn = 1

sql tsql