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
.