SQL - select distinct only on one column

distinct, sql, sql-server, unique

Solution

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

Problem

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?

Original source

Related problems