Skip to content
Advertisement

SQL Server : need help write what i think should be a simple code

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement