SQL 从每组中选择前 1 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15380718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select the top 1 row from each group
提问by Easty
I have a table that lists the versions of software that are installed:
我有一个表格，列出了已安装的软件版本：
id | userid | version | datetime
----+--------+---------+------------------------
111 | 75 | 10075 | 2013-03-12 13:40:58.770
112 | 75 | 10079 | 2013-03-12 13:41:01.583
113 | 78 | 10065 | 2013-03-12 14:18:24.463
114 | 78 | 10079 | 2013-03-12 14:22:20.437
115 | 78 | 10079 | 2013-03-12 14:24:01.830
116 | 78 | 10080 | 2013-03-12 14:24:06.893
117 | 74 | 10080 | 2013-03-12 15:31:42.797
118 | 75 | 10079 | 2013-03-13 07:03:56.157
119 | 75 | 10080 | 2013-03-13 07:05:23.137
120 | 65 | 10080 | 2013-03-13 07:24:33.323
121 | 68 | 10080 | 2013-03-13 08:03:24.247
122 | 71 | 10080 | 2013-03-13 08:20:16.173
123 | 78 | 10080 | 2013-03-13 08:28:25.487
124 | 56 | 10080 | 2013-03-13 08:49:44.503
I would like to display all fields of one record from each useridbut only the highest version (also version is a varchar).
我想显示每个记录的所有字段，userid但只显示最高版本（版本也是 a varchar）。
采纳答案by Joachim Isaksson
You're not specifying how you want ties handled, but this will do it if you want the duplicates displayed;
您没有指定您希望如何处理关系，但如果您希望显示重复项，则可以这样做；
SELECT a.* FROM MyTable a
LEFT JOIN MyTable b
ON a.userid=b.userid
AND CAST(a.version AS INT) < CAST(b.version AS INT)
WHERE b.version IS NULL
If you want to eliminate duplicates and if they exist pick the newest of them, you'll have to extend the query somewhat;
如果您想消除重复项并且如果它们存在，请选择其中最新的，您将不得不稍微扩展查询；
WITH cte AS (SELECT *, CAST(version AS INT) num_version FROM MyTable)
SELECT a.id, a.userid, a.version, a.datetime
FROM cte a LEFT JOIN cte b
ON a.userid=b.userid
AND (a.num_version < b.num_version OR
(a.num_version = b.num_version AND a.[datetime]<b.[datetime]))
WHERE b.version IS NULL
回答by Tim Schmelter
If you use SQL-Server (minimum 2005) you can use a CTEwith the ROW_NUMBERfunction. You can use CASTfor version to get the correct order:
如果您使用 SQL-Server（最低版本为 2005），您可以将 aCTE与该ROW_NUMBER函数一起使用。您可以使用CASTfor version 来获得正确的顺序：
WITH cte
AS (SELECT id,
userid,
version,
datetime,
Row_number()
OVER (
partition BY userid
ORDER BY Cast(version AS INT) DESC) rn
FROM [dbo].[table])
SELECT id,
userid,
version,
datetime
FROM cte
WHERE rn = 1
ORDER BY userid
ROW_NUMBERreturns always one record even if there are multiple users with the same (top) version. If you want to return all "top-version-user-records", you have to replace ROW_NUMBERwith DENSE_RANK.
ROW_NUMBER即使有多个用户具有相同（顶级）版本，也始终返回一条记录。如果要返回所有“top-version-user-records”，则必须替换ROW_NUMBER为DENSE_RANK.
回答by John Woo
WITH records
AS
(
SELECT id, userid, version, datetime,
ROW_NUMBER() OVER (PARTITION BY userID
ORDER BY version DESC) rn
FROM tableName
)
SELECT id, userid, version, datetime
FROM records
WHERE RN =1
回答by Rdy
I think this may solve your problem :
我认为这可以解决您的问题：
SELECT id,
userid,
Version,
datetime FROM (
SELECT id,
userid,
Version,
datetime ,
DENSE_Rank() over (Partition BY id order by datetime asc) AS Rankk
FROM [dbo].[table]) RS
WHERE Rankk<2
I used RANK function for ur requirement....
我使用 RANK 函数来满足您的要求....
回答by Malka
The following code will display what you want and is great for performance!
以下代码将显示您想要的内容并且非常适合性能！
select * from the_table t where cast([version] as int) =
(select max(cast([version] as int)) from the_table where userid = t.userid)
回答by pimbrouwers
If my experience tuning has taught me anything, generalities are bad bad bad.
如果我的调音经验教会了我什么，那么一般性就是坏坏坏坏。
BUT, If the table your getting the Top Xfrom is large (i.e. hundreds of thousands or millions). CROSS APPLYis almost universally the best. In fact, if you benchmark it, cross apply performs consistently & admirablyat smaller scales as well (in the tens of thousands) And ever covers the with tiespotential requirement.
但是，如果您Top X从中获取的表很大（即数十万或数百万）。CROSS APPLY几乎普遍是最好的。事实上，如果你的基准它，跨应用进行持续和令人钦佩的小规模，以及（在几万）和不断覆盖有关系的潜在需求。
Something like:
就像是：
select
id
,userid
,version
,datetime
from
TheTable t
cross apply
(
select top 1 --with ties
id
from
TheTable
where
userid = t.userid
order by
datetime desc
)
回答by Burleigh Bear
select l.* from the_table l
left outer join the_table r
on l.userid = r.userid and l.version < r.version
where r.version is null
