SQL Server:为结果集中的每个子组选择Max

I am new to SQL Server. I have a table that contains user names, addresses and the update dates.

我是SQL Server新手。我有一个包含用户名、地址和更新日期的表。

The following query gives me all required details from that table but my problem is that the table can contain multiple records for the same user / ID in case they changed their address more than once.

下面的查询给出了该表中所需的所有细节，但我的问题是，该表可以包含同一用户/ ID的多个记录，以防它们多次更改地址。

How can I only get the address from the latest update for each user / ID? I tried with DISTINCT, TOP1 etc. but couldn't figure out a way to make this work.

如何才能从每个用户/ ID的最新更新中获得地址?我尝试过独特的TOP1等等，但是我想不出一种方法来做这件事。

My query:

我的查询:

SELECT
    u.ID
    , u.lastName + ', ' + u.firstName AS fullName
    , u.DOB
    , u.homeAddress
    , u.updateDate AS lastUpdate
FROM dbo.Users u
GROUP BY u.ID, u.lastName, u.firstName, u.homeAddress, u.updateDate
ORDER BY u.lastName, u.firstName, updateDate DESC

4 个解决方案

#1

rextester demo

rextester演示

select * from (
select rn = row_number() over (partition by id order by updateDate desc), *
from Users
) x
where rn = 1

#2

Or you can do in an more intuitive old style fashion, just with a subselect:

或者你可以用一种更直观的老式时尚，只是用一个小的选择:

SELECT
    u.ID
    , u.lastName + ', ' + u.firstName AS fullName
    , u.DOB
    , u.homeAddress
    , u.updateDate AS lastUpdate
FROM dbo.Users u INNER JOIN
(
    -- here is where we get the last update
    select u.ID, max(u.updateDate) as lastUpdate from dbo.Users group by u.ID
) as s
on u.ID = s.ID
-- and here we force the address of the last update
WHERE u.updateDate = s.lastUpdate 
GROUP BY u.ID, u.lastName, u.firstName, u.homeAddress, u.updateDate
ORDER BY u.lastName, u.firstName, updateDate DESC

You get the last update of each ID in a subselect, and filter this date in the WHERE clause.

您将获得子select中每个ID的最后一次更新，并在WHERE子句中过滤这个日期。

#3

Go with ROW_NUMBER

与ROW_NUMBER

SELECT * FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY  u.ID ORDER BY u.updateDate DESC ) AS SNO
    , u.ID
    , u.lastName + ', ' + u.firstName AS fullName
    , u.DOB
    , u.homeAddress
    , u.updateDate AS lastUpdate
    FROM dbo.Users u
)A
WHERE A.SNO=1

#4

You need to (1) Group your user ID; (2) sort descending your updateDate and (3) DISTINCT your user ID as below:

需要(1)对用户ID进行分组;(2)排序您的updateDate和(3)区分您的用户ID如下:

SELECT
    DISTINCT u.ID
    , u.lastName + ', ' + u.firstName AS fullName
    , u.DOB
    , u.homeAddress
    , u.updateDate AS lastUpdate
FROM dbo.Users u
GROUP BY u.ID
ORDER BY u.updateDate DESC

#1