CODEWITHSUNDEEP
sql-server
Volker
Volker

Reputation: 465

SQL group need related value instead of aggregate function

Have a table like this

FileID  Value    Version
-------------------------
1       Welle       2
1       Achse       3
2       Box         5
2       Enclosure   7

I need to "sum" up the lines with same FileID -> take highest value from column VERSION and get back the related value.

Desired result would be:

FileID  Value    Version
-------------------------
1       Achse       3
2       Enclosure   7

However using GROUP By sums up, but brings wrong result for Value:

SELECT 
    [FileID],
    MAX([Value]),
    MAX([Version])
FROM [ValueMist]
GROUP BY FileID

This returns:

 FileID  Value   Version
 ------------------------
 1       Welle     3
 2       Enclosure 7

Upvotes: 1

Views: 48

Answers (2)

Thiyagu
Thiyagu

Reputation: 1340

You can achieve this by using ROW NUMBER

     ;WITH CTE AS (SELECT ROW_NUMBER() OVER ( PARTITION BY 
      ID ORDER BY VERSION DESC) AS RW 
       FROM TABLE)

       SELECT * FROM CTE
        WHERE RW=1

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81990

One option is WITH TIES in concert with row_number()

Example

Select top 1 with ties *
 From  YourTable
 Order By row_number() over (partition by FileId Order By version desc)

Upvotes: 1

Related Questions