With ties clause in sqlserver

SELECT TOP(6) WITH TIES COL
FROM (
SELECT 1 COL UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 4 UNION ALL
SELECT 3 UNION ALL
SELECT 4
) A
ORDER BY COL

Answer:

1,2,3,3,4,4,4

Explanation:

Using TOP with "WITH TIES" give all matching values with the last TOP (n) rows in ORDER BY columns. In simple way, if you will specify top 3 then it will give result 1,2,3,3 as there are two same value in that column. For top 5, it will give the result as 1,2,3,3,4,4,4 because top 5th value is 4. It will find same value in the column used in ORDER BY until the last row of the table. That's why top 6 returned 1,2,3,3,4,4,4 as top 6th is 4 so it will look for value 4 in entire COL column values. If matches are found, then it will include all those as well. Refs: http://msdn.microsoft.com/en-IN/library/ms189463(v=sql.90).aspx http://blog.sqlauthority.com/2009/12/23/sql-server-order-by-clause-and-top-with-ties/