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

Question

Say I have a customer address table:

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

In a table, a single client, such as John Smith, can have multiple addresses. I need a select query for this table to return only the first row found where there are duplicates in "CName". For this table, it should return all rows except the third (or the 1st — either of these two addresses is OK, but only one can be returned). Is there a keyword that I can add to the SELECT query for filtering based on the fact that the server has already seen the column value before?

Answer 1

A very simple answer if you say that you don’t care what address is used.

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

If you want the first to match, say, an “inserted” column, then this is 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 

Answer 2

In SQL 2k5 +, you can do something like:

 ;with cte as ( select CName, AddressLine, rank() over (partition by CName order by AddressLine) as [r] from MyTable ) select CName, AddressLine from cte where [r] = 1 

Answer 3

You can use row_number() to get the row number of a row. It uses the over command - the partition by clause indicates when to restart the numbering, and order by selects what is needed to order the line number. Even if you added order by at the end of your query, it would preserve order in the over command when numbering.

 select * from mytable where row_number() over(partition by Name order by AddressLine) = 1