SQL - select distinct only on one column

Question

I have searched far and wide for an answer to this problem  I m using a Microsoft SQL Server  suppose I have a table that looks like this    -------- --------- ------------- -------------    ID       NUMBER    COUNTRY       LANG           -------- --------- ------------- -------------    1        3968      UK            English         2        3968      Spain         Spanish         3        3968      USA           English         4        1234      Greece        Greek           5        1234      Italy         Italian         I want to perform one query which only selects the unique  NUMBER  column  whether is be the first or last row doesn t bother me   So this would give me    -------- --------- ------------- -------------    ID       NUMBER    COUNTRY       LANG           -------- --------- ------------- -------------    1        3968      UK            English         4        1234      Greece        Greek           How is this achievable

User · Answer

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.

User · Answer

You will use the following query    SELECT   FROM  table  GROUP BY NUMBER    Where  table  is the name of the table    This provides a unique listing for the NUMBER column however the other columns may be meaningless depending on the vendor implementation  which is to say they may not together correspond to a specific row or rows

User · Answer

Since you don t care  I chose the max ID for each number   select tbl   from tbl inner join   select max id  as maxID  number from tbl group by number  maxID on maxID maxID   tbl id     Query Explanation   select      tbl    -- give me all the data from the base table  tbl    from      tbl         inner join    -- only return rows in tbl which match this subquery         select              max id  as maxID -- MAX  ie distinct  ID per GROUP BY below         from              tbl          group by              NUMBER            -- how to group rows for the MAX aggregation       maxID         on maxID maxID   tbl id -- join condition ie only return rows in tbl                                  -- whose ID is also a MAX ID for a given NUMBER

[sql] SQL - select distinct only on one column

Examples related to sql

Examples related to sql-server

Examples related to unique

Examples related to distinct