从每个组中选择前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。

An SQLfiddle to test with.

如果你想消除重复，如果它们存在挑选最新的，你必须扩大查询;

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屋！