TOP ... WITH TIES
TOP ... WITH TIES is a SQL clause in the SQL Server Specific category. SQL Server: returns top n rows plus any additional rows that have the same sort value as the nth row. The syntax is SELECT TOP n WITH TIES columns FROM table ORDER BY column;. It returns top rows with ties. A typical example: SELECT TOP 3 WITH TIES name, salary FROM employees ORDER BY salary DESC; -- If salaries are: 100, 95, 95, 90, 85 -- Returns: 100, 95, 95 (all tied for 3rd place) -- Without WITH TIES: 100, 95, 90 A close relative is OUTPUT Clause, which sQL Server: returns values from DML statements (like PostgreSQL RETURNING). A close relative is STUFF(), which sQL Server: deletes a specified length of characters from a string and inserts another string at the start position. A close relative is FOR XML PATH, which sQL Server: converts query results to XML format. Commonly used for string concatenation hack.