如何在其中一列具有相同值的行中查询具有最高列值的行 [英] How to query for rows that have highest column value among rows that have same value for one of the columns

问题描述

我有 UserScores 表，其中包含如下数据:

I have UserScores Table with data like this:

Id    userId   Score
1       1       10
2       2       5
3       1       5

我想要一个可以给我以下输出的查询或 SQL 块

I would like to have a query or SQL block that can give me the following output

Id    userId   Score
3       1       5
2       2       5

也就是说，我想选择属于最高id"列值的user id"唯一的行.

That is, I would like to pick rows that are unique by 'user id' that belonging to the highest 'id' column value.

推荐答案

另一种适用于 SQL Server 2000 的解决方案(与上面的 INNER JOIN 相同，但速度稍快)是:

Another solution that would work on SQL Server 2000 (same as INNER JOIN above, but slightly faster) is:

SELECT id, userId, Score
  FROM UserScores
 WHERE id in (SELECT MAX(id)
                FROM UserScores
               GROUP BY userId
             ) 
 ORDER BY userId

这篇关于如何在其中一列具有相同值的行中查询具有最高列值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！