sql server - SQL - select distinct only on one column

Question

(This question already has answers here): question from:https://stackoverflow.com/questions/20406069/sql-select-distinct-only-on-one-column

Answer 1

A very typical approach to this type of problem is to use row_number():

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

This is more generalizable than using a comparison to the minimum id. For instance, you can get a random row by using order by newid(). You can select 2 rows by using where seqnum <= 2.