TOP WITH TIES Clause in sql Server

WITH TIES Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

See the example

Using Top clause

USE AdventureWorks2012_CS; SELECT TOP(6)  ProductNumber, Name, ListPrice,  CONVERT(VARCHAR(10),SellStartDate,1) SellStartDate FROM Production.Product ORDER BY ListPrice DESC

It returns 6 records.

But if we use WITH TIES clause it will return the number of record which rows have equal values in the columns used in the ORDER BY clause

USE AdventureWorks2012_CS; SELECT TOP(6) WITH TIES  ProductNumber, Name, ListPrice,  CONVERT(VARCHAR(10),SellStartDate,1) SellStartDate FROM Production.Product ORDER BY ListPrice DESC

See the output

Here I am using order by clause on List Price column.

See the other example if we use order by clause on Product number let’s see the output.

It will return 6 records because Product number doesn’t duplicate value.