Get top 1 results from sql results based on the highest version in sql server

Question

I have a basic query where I see a list of usernames and versions of an app they are using:

Username AppVersion  Email           First Name
-------- ----------  -----           ----------
user1    2.3         user1@aol.com   User 1
user1    2.4         user1@aol.com   User 1
user1    2.5         user1@aol.com   User 1
user2    2.3         user2@aol.com   User 2
user2    2.4         user2@aol.com   User 2
user3    2.4         user3@aol.com   User 3
user3    2.5         user3@aol.com   User 3

My SQL is:

SELECT TOP 100 LoginLog.SalesRepID, LoginLog.AppVersion FROM  LoginLog
GROUP BY LoginLog.SalesRepID, LoginLog.AppVersion
ORDER BY SalesRepID, LoginLog.AppVersion DESC

But what I really want from this list is the newest version of the app that the user is on, so my result would really be:

Username AppVersion  Email           First Name
-------- ----------  -----           ----------
user1  2.5         user1@aol.com   User 1
user2  2.4         user2@aol.com   User 2
user3  2.5         user3@aol.com   User 3

How do I modify this query to show that kind of result?

EDIT:

I apologize, I was not clear enough here - I tried to simplify my question and should not have. There are a couple of additional columns in this example I left out- #FACEPALM

See revised above - sorry everyone!!!

Where is Username on your query? you should group by username and get max of appversion — Mehdi Yeganeh, May 15 '13 at 21:41
@Slee: It doesn't matter what columns you want if you use the `CTE`+`ROW_NUMBER` approach. — Tim Schmelter, May 15 '13 at 22:07

score 4 · Accepted Answer · edited May 23 '17 at 12:29

4

Use a common table expression with ROW_NUMBER:

WITH cte 
     AS (SELECT Username, 
                AppVersion, 
                RN = Row_number() 
                       OVER ( 
                         partition BY username 
                         ORDER BY Cast('/' + Replace(AppVersion, '.', '/') + '/' 
                       AS 
                       HIERARCHYID) 
                       DESC 
                       ) 
         FROM   loginlog) 
SELECT Username, AppVersion FROM  CTE
WHERE  RN = 1 
ORDER BY UserName

DEMO

Credits for the version sort here:

How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query

edited May 23 '17 at 12:29

Community

1
1

answered May 15 '13 at 21:41

Tim Schmelter

450,073
74
686
939

+1, but you forgot to order by `UserName` on your final `SELECT`. – Cᴏʀʏ May 15 '13 at 21:45
@Cory: Thanks, edited. However, also edited to use the version sort which i've found [**here**](http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query)(i'm usually on sql-server 2005). – Tim Schmelter May 15 '13 at 21:50

Cᴏʀʏ · Answer 2 · 2013-05-15T21:48:07.720

Assuming your [AppVersion] column is a string, I've added some conversion. When using aggregate functions such as MAX(), those columns should be excluded from your GROUP BY clause. Also, to get the ORDER BY in the right order, the same conversion should go in that clause as well.

SELECT TOP 100 
    SalesRepID
   ,MAX(CONVERT(float, LoginLog.AppVersion))
FROM  
    LoginLog
GROUP BY 
    SalesRepID
ORDER BY 
    SalesRepID, CONVERT(float, LoginLog.AppVersion)

EDIT This won't work if the application version numbers include minor revisions (e.g. 3.4.2). Tim's approach will work better in that situation.

I had to revise my question, sorry about that – Slee May 15 '13 at 21:59 — Slee, May 15 '13 at 21:59

score 0 · Answer 3 · answered May 15 '13 at 21:40

0

Assuming AppVersion is a numeric-type field, use the MAX() function, and group on ONLY the SalesRepIDs:

SELECT TOP 100 LoginLog.SalesRepID, MAX(LoginLog.AppVersion)
FROM  LoginLog
GROUP BY LoginLog.SalesRepID
ORDER BY SalesRepID

answered May 15 '13 at 21:40

Marc B

356,200
43
426
500

Get top 1 results from sql results based on the highest version in sql server

EDIT:

3 Answers3

Linked