如何返回多列，但删除1列的重复项

I'm trying to write a query to return all columns, and filter by displaying DISTINCT records from a specific column.

我正在尝试编写一个查询来返回所有列，并通过显示特定列的DISTINCT记录进行过滤。

Here is my Query right now, which requires me to "Group By" everything in the select statement. I obviously don't want to do this.:

这是我现在的查询，这需要我在select语句中“分组”所有内容。我显然不想这样做：

SELECT     lVisitID, sFirstName, sLastName, sAddress1, sStoreNumber
FROM        Customers
WHERE     (dtServiceDate < '5/1/15') AND (sStoreNumber = '123') 
GROUP BY sAddress1

I've also tried the below, but that returns duplicates:

我也尝试了下面的内容，但是返回重复内容：

    SELECT     lVisitID, MAX(sAddress1)
    FROM        Customers
    WHERE     (dtServiceDate < '5/1/15') AND (sStoreNumber = '123') 
    GROUP BY sAddress1, lvisitID

Here is my data:

这是我的数据：

lVisitID     sFirstName   sLastName     sAddress1     sStoreNumber
  1             Bob          Jones         14 Place     123
  2             Jim          Bibby         12 Place     123
  3             John         Smith         12 Place     123
  4             Jen         Jones          22 Place     193
  6             Kim         Smith          15 Place     123

The idea here would be to return Only distinct addresses, and the store number. When I attempt the above, I need:

这里的想法是返回只有不同的地址和商店号码。当我尝试上述内容时，我需要：

  1             Bob          Jones         14 Place     123
  2             Jim          Bibby         12 Place     123
  6             Kim         Smith          15 Place     123

2 个解决方案

#1

If you want just the address that has the most recent visit, use row_number():

如果您只想要最近访问的地址，请使用row_number（）：

select c.*
from (select c.*,
             row_number() over (partition by address order by lVisitId desc) as seqnum
      from customers c
     ) c
where seqnum = 1;

#2

I tried this on Postgres SQL.

我在Postgres SQL上试过这个。

SELECT     Distinct on (sAddress1) *
FROM        Customers
WHERE     (dtServiceDate < '5/1/15') AND (sStoreNumber = '123')

#1