Select the top 1 row from each group

Question

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 userid but only the highest version (also version is a varchar).

Answer 1

If you use SQL-Server (minimum 2005) you can use a CTE with the ROW_NUMBER function. You can use CAST for version to get the correct order:

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

Demo

ROW_NUMBER returns 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_NUMBER with DENSE_RANK.