如何只为列的每个唯一值选择第一行? [英] 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          |
+-----------------------+------------------------+

在表格中，像 John Smith 这样的一位客户可以有多个地址.我需要该表的 SELECT 查询以仅返回在CName"中有重复项的第一行.对于这个表，它应该返回除第三行(或第一行 - 这两个地址中的任何一个都可以，但只能返回一个)之外的所有行.

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).

是否可以在 SELECT 查询中添加关键字以根据服务器之前是否已经看到该列值进行过滤?

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?

推荐答案

如果您说您不在乎使用哪个地址，这是一个非常简单的答案.

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

这篇关于如何只为列的每个唯一值选择第一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！