Get the first 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 the application they use:

Username AppVersion Email First Name -------- ---------- ----- ---------- user1 2.3 [e-mail] User 1 user1 2.4 [e-mail] User 1 user1 2.5 [e-mail] User 1 user2 2.3 [e-mail] User 2 user2 2.4 [e-mail] User 2 user3 2.4 [e-mail] User 3 user3 2.5 [e-mail] User 3 

My SQL:

 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 latest version of the application in which the user is included, so my result will be valid:

 Username AppVersion Email First Name -------- ---------- ----- ---------- user1 2.5 [e-mail] User 1 user2 2.4 [e-mail] User 2 user3 2.5 [e-mail] User 3 

How do I modify this query to show this result?

EDIT:

I'm sorry, I was not clear enough here. I tried to simplify my question and should not have. There are several additional columns in this example - #FACEPALM

See revised above - sorry everyone !!!

Answer 1

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 here:

How can I sort the column "Version Number" in general with a SQL Server query

Answer 2

Assuming your [AppVersion] column is a row, I added some conversion. When using aggregate functions such as MAX() , these columns should be excluded from your GROUP BY . In addition, in order to get ORDER BY in the correct order, the same conversion should also go in this section.

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

EDIT This will not work if application version numbers include minor changes (e.g. 3.4.2 ). Tim's approach will work better in this situation.

Answer 3

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

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