← Back to Knowledge Base← Previous questionNext question →
ProgrammingBI/Reporting Analyst

How does the WITH TIES option work and what is it used for in SQL when sorting and selecting a limited number of rows, and what mistakes are encountered due to misunderstanding this option?

Pass interviews with Hintsage AI assistant

Answer.

In SQL, the WITH TIES option is used in conjunction with ORDER BY and the row limiting construct (TOP or FETCH FIRST ... ROWS ONLY). It allows returning not only the strict N top rows but also all rows that have sorting field values matching the last (N-th) row.

When it's needed: in analytics, rankings, leaderboards, when it's important to include "ties" or groups with the same ranking.

Example (SQL Server):

SELECT TOP 3 WITH TIES *
FROM Sales
ORDER BY Amount DESC;

If the 3rd and 4th places have the same sales amount, both rows will be returned.

In PostgreSQL:

SELECT * FROM Sales
ORDER BY Amount DESC
FETCH FIRST 3 ROWS WITH TIES;

Important: it will work only with ORDER BY, otherwise the result is unpredictable.

Trick question.

What is the difference between just TOP N/LIMIT and using WITH TIES?

A common incorrect answer: "WITH TIES simply limits rows, it’s a synonym".

Correct answer:

  • TOP N/LIMIT — will return exactly N rows, even if the subsequent rows have the same sorting value.
  • WITH TIES — will add all rows that have sorting field values matching the last (N-th).

Example:

-- Select the top 2 results, but if there is a "tie" — also get them:
SELECT TOP 2 WITH TIES Name, Score FROM Results ORDER BY Score DESC;

Examples of real mistakes due to lack of knowledge on the topic.

Story

Project: Student ranking competition. Mistake: Used just LIMIT 3, and several students with the same score ended up outside the winners' list — actual prize winners were erroneously "cut off".

Story

Project: E-commerce analytics. Mistake: Wanted to show the top 5 popular products, but for the same SKU based on sales only showed the first 5, and later during an audit noticed that some hits were not included in the report.

Story

Project: Branch sales reporting. Mistake: The client expected to see all leaders with equal results, but the developer was unaware of WITH TIES — the results were incorrect, and the client filed a complaint about data quality.