When using the TOP clause in a query in SQL Server, you may encounter occasions where two or more results tie for last place. You probably wouldn’t even know when this happens, because the default behavior of TOP is to return no more than the number of rows you specify.

The TOP clause accepts a WITH TIES argument that allows you to specify whether or not to include all results that tie for last place. Rows can tie for last place due to their ORDER BY column containing the same value. Using this argument may therefore result in more rows being returned than you actually specified.

Example 1 – The Data

First, here’s the data we’ll work with in the following examples:

SELECT 
  AlbumId,
  AlbumName,
  ArtistId 
FROM Albums;

Result:

+-----------+--------------------------+------------+
| AlbumId   | AlbumName                | ArtistId   |
|-----------+--------------------------+------------|
| 1         | Powerslave               | 1          |
| 2         | Powerage                 | 2          |
| 3         | Singing Down the Lane    | 6          |
| 4         | Ziltoid the Omniscient   | 5          |
| 5         | Casualties of Cool       | 5          |
| 6         | Epicloud                 | 5          |
| 7         | Somewhere in Time        | 1          |
| 8         | Piece of Mind            | 1          |
| 9         | Killers                  | 1          |
| 10        | No Prayer for the Dying  | 1          |
| 11        | No Sound Without Silence | 9          |
| 12        | Big Swing Face           | 4          |
| 13        | Blue Night               | 12         |
| 14        | Eternity                 | 12         |
| 15        | Scandinavia              | 12         |
| 16        | Long Lost Suitcase       | 7          |
| 17        | Praise and Blame         | 7          |
| 18        | Along Came Jones         | 7          |
| 19        | All Night Wrong          | 3          |
| 20        | The Sixteen Men of Tain  | 3          |
| 21        | Yo Wassup                | 9          |
| 22        | Busted                   | 9          |
+-----------+--------------------------+------------+

Example 2 – Use TOP Without Ties

Here’s what happens if we use TOP without specifying WITH TIES. This is the way most people use this clause.

In this case, I order the results by ArtistId.

SELECT TOP(3)
  AlbumId,
  AlbumName,
  ArtistId 
FROM Albums
ORDER BY ArtistId ASC;

Result:

+-----------+-------------------+------------+
| AlbumId   | AlbumName         | ArtistId   |
|-----------+-------------------+------------|
| 1         | Powerslave        | 1          |
| 7         | Somewhere in Time | 1          |
| 8         | Piece of Mind     | 1          |
+-----------+-------------------+------------+

As expected, we get three rows. These are the top three as specified by the TOP clause.

Example 3 – Use TOP With Ties

Now for the ties. Here’s what happens if we add WITH TIES.

SELECT TOP(3) WITH TIES
  AlbumId,
  AlbumName,
  ArtistId 
FROM Albums
ORDER BY ArtistId ASC;

Result:

+-----------+-------------------------+------------+
| AlbumId   | AlbumName               | ArtistId   |
|-----------+-------------------------+------------|
| 1         | Powerslave              | 1          |
| 7         | Somewhere in Time       | 1          |
| 8         | Piece of Mind           | 1          |
| 9         | Killers                 | 1          |
| 10        | No Prayer for the Dying | 1          |
+-----------+-------------------------+------------+

We now get five rows instead of just three. This is because there are two more rows that share the same ArtistId as the third row. In other words, three rows were tying for last place.

Note that in SQL Server, the returned order of tying rows is arbitrary.

Example 4 – A Modified ORDER BY Clause to Eliminate Ties

If we add the AlbumId column to the ORDER BY clause, this eliminates the ties altogether.

SELECT TOP(3) WITH TIES
  AlbumId,
  AlbumName,
  ArtistId 
FROM Albums
ORDER BY ArtistId ASC, AlbumId ASC;

Result:

+-----------+-------------------+------------+
| AlbumId   | AlbumName         | ArtistId   |
|-----------+-------------------+------------|
| 1         | Powerslave        | 1          |
| 7         | Somewhere in Time | 1          |
| 8         | Piece of Mind     | 1          |
+-----------+-------------------+------------+

So even when we specify WITH TIES, none are present in this case.

Example 5 – Using a WHERE Clause

Here’s one last example, where I use a WHERE clause to extract a bunch of rows from the middle of the table. The first example is without ties, and the second is with ties.

Without ties:

SELECT TOP(4)
  AlbumId,
  AlbumName,
  ArtistId 
FROM Albums
WHERE AlbumId > 10
ORDER BY ArtistId ASC;

Result:

+-----------+-------------------------+------------+
| AlbumId   | AlbumName               | ArtistId   |
|-----------+-------------------------+------------|
| 19        | All Night Wrong         | 3          |
| 20        | The Sixteen Men of Tain | 3          |
| 12        | Big Swing Face          | 4          |
| 16        | Long Lost Suitcase      | 7          |
+-----------+-------------------------+------------+

With ties:

SELECT TOP(4) WITH TIES
  AlbumId,
  AlbumName,
  ArtistId 
FROM Albums
WHERE AlbumId > 10
ORDER BY ArtistId ASC;

Result:

+-----------+-------------------------+------------+
| AlbumId   | AlbumName               | ArtistId   |
|-----------+-------------------------+------------|
| 19        | All Night Wrong         | 3          |
| 20        | The Sixteen Men of Tain | 3          |
| 12        | Big Swing Face          | 4          |
| 16        | Long Lost Suitcase      | 7          |
| 17        | Praise and Blame        | 7          |
| 18        | Along Came Jones        | 7          |
+-----------+-------------------------+------------+