Compare top vs top with ties:
In SQL,
TOP is used to retrieve a specified number of rows,
while TOP WITH TIES returns the top rows along with any
additional rows that have the same value as the last row in the top set,
potentially returning more than the specified TOP number.
Example:
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC will
return the top 5 highest salaries.
SELECT TOP 5 WITH TIES * FROM Employees ORDER BY Salary DESC
will return the top 5 highest salaries and any additional employees with
the same salary as the 5th highest.
TOP (n):
- Returns the first n rows of the result set, based on the ORDER BY clause.
- If there are ties for the nth row, only the first n rows are returned, and the tied rows are not included.
TOP WITH TIES:
- Returns the first n rows, as with TOP.
- Includes all rows that have the same value as the last row in the TOP n set, even if it exceeds n.