Simon Harriyott

With Ties - SQL Server Tip

Consider the following table of data from my database:

Name Score
Brenda 42
Maureen 41
Edwin 41
Terry 40
Rupert 39
Arthur 39
Paula 39
Eric 38



These are my fictional students from my fictional cooking class, with their fictional scores from my fictional washing-up exam.

Suppose I wanted to find the top five students. I would do a SELECT TOP 5 statement, thus:

SELECT TOP 5 Name, Score
FROM Scores
ORDER BY Score DESC

This results in the following:

Name Score
Brenda 42
Maureen 41
Edwin 41
Terry 40
Rupert 39



Note that although there are 3 students with a score of 39, only Rupert made it onto the top 5. To get Arthur and Paula too, then one can use WITH TIES as part of the TOP clause:

SELECT TOP 5 WITH TIES Name, Score
FROM Scores
ORDER BY Score DESC

Which gives:

Name Score
Brenda 42
Maureen 41
Edwin 41
Terry 40
Rupert 39
Arthur 39
Paula 39



I only found this one out today. Interesting.

[Tags: ]