Search code examples
sqlsql-servergroup-bycountwindow-functions

Find tie values in SQL Server

I have a table such as the following:

student quizId score
A 1 90
B 1 95
A 2 87
B 2 87

I want to get this result:

quizId highestGrade isTie
1 95 0
2 87 1

Is there any simple way to do this besides RANK WITH TIES?

Solution

  • Use aggregation to get the number of students for each combination of quizId and score and MAX() window function to get the highestGrade.

    A CASE expression may be used to get the column isTie:

    SELECT DISTINCT quizId,
       MAX(score) OVER (PARTITION BY quizId) AS highestGrade,
       CASE WHEN COUNT(*) = 1 THEN 0 ELSE 1 END AS isTie
FROM tablename
GROUP BY quizId, score;

    See the demo.