Search code examples
sqldistinct

How to select distinct records with preference depending on a value

i have this table : Table 1

notice that some ids have double records with IsTop = 1 if i have this kind of scenario im interested in selecting the one that has IsTop = 1 and if i dont im interested in keeping the one that IsTop = 0.

The goal is to have distinct Id's but take IsTop = 1 is it exists.

How do i do that?

Solution

  • You can use row_number():

    select t.*
from (select t.*,
             row_number() over (partition by id order by isTop desc) as seqnum
      from t
     ) t
where seqnum = 1;