sqlsql-servert-sqlselectgreatest-n-per-group

How to select only the first rows for each unique value of a column?


Let's say I have a table of customer addresses:

CName AddressLine
John Smith 123 Nowheresville
Jane Doe 456 Evergreen Terrace
John Smith 999 Somewhereelse
Joe Bloggs 1 Second Ave

In the table, one customer like John Smith can have multiple addresses. I need the SELECT query for this table to return only first row found where there are duplicates in 'CName'. For this table it should return all rows except the 3rd (or 1st - any of those two addresses are okay but only one can be returned).

Is there a keyword I can add to the SELECT query to filter based on whether the server has already seen the column value before?


Solution

  • A very simple answer if you say you don't care which address is used.

    SELECT
        CName, MIN(AddressLine)
    FROM
        MyTable
    GROUP BY
        CName
    

    If you want the first according to, say, an "inserted" column then it's a different query

    SELECT
        M.CName, M.AddressLine,
    FROM
        (
        SELECT
            CName, MIN(Inserted) AS First
        FROM
            MyTable
        GROUP BY
            CName
        ) foo
        JOIN
        MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted