CODEWITHSUNDEEP
sqlsql-server
Slee
Slee

Reputation: 28248

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

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         [email protected]   User 1
user1    2.4         [email protected]   User 1
user1    2.5         [email protected]   User 1
user2    2.3         [email protected]   User 2
user2    2.4         [email protected]   User 2
user3    2.4         [email protected]   User 3
user3    2.5         [email protected]   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         [email protected]   User 1
user2  2.4         [email protected]   User 2
user3  2.5         [email protected]   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!!!

Upvotes: 2

Views: 104

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460138

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

Upvotes: 4

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107536

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.

Upvotes: 2

Marc B
Marc B

Reputation: 360702

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

Upvotes: 0

Related Questions