I have a simple table:
id id_fk default -- ----- ------- 1 1 T 2 1 F 3 2 T 4 3 T 5 3 F
I would like to return one row for each id_fk. If the default is T then return that one. If their is no default T then return default F.
It seems simple enough but I have been struggling.
Advertisement
Answer
One option is to filter with a correlated subquery:
select t.*
from mytable t
where t.id = (
select top(1) t1.id
from mytable t1
where t1.id_fk = t.id_fk
oder by t1.default desc, t1.id
)
This produces one record for each id_fk: priority is given to the record that has 'T'as default, and then to the smalles id.