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: sql server with ties]