从每组中选择前 1 行 [英] Select the top 1 row from each group

问题描述

我有一张表格，列出了已安装的软件版本:

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

我想显示每个 userid 中一个记录的所有字段，但只显示最高版本(版本也是 varchar).

I would like to display all fields of one record from each userid but only the highest version (also version is a varchar).

推荐答案

您没有指定要如何处理关系，但如果您希望显示重复项，则可以这样做；

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

用于测试的 SQLfiddle.

如果您想消除重复项并且如果它们存在，请选择其中最新的，您将不得不稍微扩展查询；

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

另一个 SQLfiddle.

这篇关于从每组中选择前 1 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！