SQL查询:只显示每组的最新Id

GROUP BY 方法可能更容易使用。

SELECT *
FROM (
    SELECT Name, Description, Id, UserId, CreatedDate
        , ROW_NUMBER() OVER (PARTITION BY Name ORDER BY CreatedDate DESC) AS rn
    FROM myTable
) s1
WHERE rn = 1

ROW_NUMBER()

感谢大家指出正确的方向。我用下面的Linq和C代码实现了这个功能:

 var results = response.GroupBy(row => row.Name)
                   .SelectMany(g => g.OrderByDescending(row => row.Id).Take(1));

对于最初的测试,这似乎是有效的。如果你认为有问题,请告诉我。

这应该是一个通用的SQL答案:

SELECT * FROM yourtable Y1
WHERE Id = (SELECT MAX(Id)
              FROM yourtable Y2
             WHERE Y2.Name = Y1.Name)

如果是MS SQL,您可以使用命令分区,否则最有效的方法是:

select * from Table
where Id in (
    select Max(Id) from Table
    Group By Name
)

不确定是否可以将Name排除在Select语句之外,您可能需要执行以下操作:

select * from Table
where Id in (
    Select Id from 
        (select Name, Max(Id) as Id from Table
        Group By Name)
)