Use of WITH TIES keyword with SELECT TOP

TOP with “WITH TIES” can be used only in SELECT statements and also with ORDER BY clause. Without SELECT TOP and ORDER BY clause we could not use “WITH TIES” keyword.

The main use of “WITH TIES” keyword is to retrieve the top values with duplicates in it. Let us see with an example.

First create a new table with some duplicate records. I have created a table named “Students” with five records in it.

Download the demo table along with the schema and data for the table using the given link below.

Download Schema and Data for Table

EXAMPLE for WITH TIES

The SELECT TOP N query always returns exactly N records. The following example defines the TOP clause.

select * from Students

select top (3)
StudName from Students order by StudName

 

Output

Output1 of select top alone

In the above example, we can encounter the situation in which use of the top clause returns exactly N records and drops any record(s) arbitrarily that has the same value as the last record in the result set. Suppose the student table has Name Oviya and the table contains one more student name with the same name, but it will not be in the result since they are ignored by the TOP clause.

In order to avoid this issue we go with SELECT TOP WITH TIES keyword.

The final result set is returned based on the field name specified with the Order by clause.

select * from Students

select top (3) with ties 
StudName from Students order by StudName

 

Output

In the below output the actual TOP (3) will display first three rows by using WITH TIES keyword we retrieved the duplicate record of the student with the same name ‘Oviya’ hence the output has 4 records.

Output2 of select top WITH TIES