TOP WITH TIES in SQL SERVER

 the clause WITH TIES can be used only with TOP and ORDER BY, both the clauses are required. Let us understand from one simple example how this clause actually works. Suppose we have 100 rows in the table and out of that 50 rows have same value in column which is used in ORDER BY; when you use TOP 10 rows, it will return you only 10 rows, but if you use TOP 10 WITH TIES, it will return you all the rows that have same value as that of the last record of top 10 — which means a total of 50 records.

get your hands dirty by trying

USE AdventureWorks;
GO
-- Example of Top 10 Records
SELECT TOP 10 *FROM Sales.SalesOrderDetailORDER BY OrderQty
GO-- Example of Top 10 WITH TIES
SELECT TOP 10 WITH TIES *
FROM Sales.SalesOrderDetail
ORDER BY OrderQty
GO

further reading: http://blog.sqlauthority.com/2009/12/23/sql-server-order-by-clause-and-top-with-ties/