CODEWITHSUNDEEP
sqlsql-server
jbassking10
jbassking10

Reputation: 803

SQL Server : remove duplicates with MAX

Given a table, Scores, I want to select the ID for the max(score) for a given GID, but in the case of a duplicate score, I want it to return the ID for the min(lastplayed) date.

ID    GID   SCORE    LASTPLAYED
==    ===   =====    ==========
1     ABC    100     2017-12-13
2     ABC     95     2017-12-15
3     ABC    100     2017-12-22

I want ID = 1

Below is something like what I want except being ID is unique, every row is returned. Removing ID from the select will give me the highest score with the earliest date but unfortunately, that's not what I want.

The ultimate goal is to only return the ID column for row 1.

select ID, max(Score), min(LastPlayed) 
from Scores 
where GId = 'ABC' 
group by ID

Upvotes: 0

Views: 1113

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use a ranking function like row_number in order by.

select top 1 with ties *
from tbl
order by row_number() over(partition by gid order by score desc,last_played)

Upvotes: 1

Related Questions