I have a simple table:
x
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
.