CODEWITHSUNDEEP
t-sql
JayTee
JayTee

Reputation: 2806

TSQL in a 3 column table, I need the MAX score with the earliest

I have a 3 column table that shows a person's score and the ID representing the record of their "test attempt".

TABLE1
empid   score   attempt_id
1       10565   10001
1       10700   10010
1       12500   10009
1       13000   10025
1       13000   10021
2       10565   10041
2       10700   10020
2       12500   10029
3       13000   10035
4       13000   10051

I'm trying to pull a recordset that contains the employee id along with their maximum score and smallest attempt_id (if there are multiple records with the same max score).

Result
empid   score   attempt_id
1       13000   10021
2       12500   10029
3       13000   10035
4       13000   10051

I can't seem to get the right SQL.

Any help?

Upvotes: 0

Views: 28

Answers (1)

Jim Horn
Jim Horn

Reputation: 889

Give this a whirl.. Get the max score and put it in a subquery, then in the main query join to it and get the min attempt.

SELECT ms.empid, ms.max_score, MIN(attempt_id) FROM Table1 ma JOIN ( SELECT empid, Max(score) as max_score FROM Table1 GROUP BY empid ) ms ON ma.empid = ms.empid AND ma.score = ms.max_score GROUP BY ms.empid, ms.max_score ORDER BY ms.empid

Upvotes: 1

Related Questions