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

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

SELECT    CName, MIN(AddressLine)FROM    MyTableGROUP 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

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, AddressLinefrom ctewhere [r] = 1

You can use the row_number() over(partition by ...) syntax like so:

select * from(select *, ROW_NUMBER() OVER(PARTITION BY CName ORDER BY AddressLine) AS rowfrom myTable) as awhere row = 1

What this does is that it creates a column called row, which is a counter that increments every time it sees the same CName, and indexes those occurrences by AddressLine. By imposing where row = 1, one can select the CName whose AddressLine comes first alphabetically. If the order by was desc, then it would pick the CName whose AddressLine comes last alphabetically.