How to select only the first rows for each unique

问题:

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?

回答1:

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

回答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

回答3:

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

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

回答4:

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

select * from
(
select *
, ROW_NUMBER() OVER(PARTITION BY CName ORDER BY AddressLine) AS row
from myTable
) as a
where 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.

回答5:

This will give you one row of each duplicate row. It will also give you the bit-type columns, and it works at least in MS Sql Server.

(select cname, address 
from (
  select cname,address, rn=row_number() over (partition by cname order by cname) 
  from customeraddresses  
) x 
where rn = 1) order by cname

If you want to find all the duplicates instead, just change the rn= 1 to rn > 1. Hope this helps