TOP, WITH TIES and ORDER BY? What is WITH TIES clause in SQL Server?

WITH TIES can be used only when TOP and ORDER BY clauses are present in SELECT statement, both the clauses are required in order to use WITH TIES.

Let understand this by taking a small example: Here we are having a table parent_tab with following records:

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           sneha

3           pratik

3           pratik

1           sneha

4           chitrangada

5           chitrangada

(6 row(s) affected)

Now I am firing a select query with Top clause to retrieve the topmost record from the table [parent_tab] where the name is equal to ‘chitrangada’.

select top 1  * from parent_tab where name like 'chitrangada'

first_id    name

----------- --------------------------------------------------

4           chitrangada

(1 row(s) affected)

Here we can see the top most record has been arrived by the above query.

Now If I want to retrieve the top most record and all the records in the table where the name is equal to ‘chitrangada’?

If  such is my requirement then ‘WITH TIES’ will help you out, let see how:

select top 1 with ties  * from parent_tab where name like 'chitrangada' order by name

first_id    name

----------- --------------------------------------------------

4           chitrangada

5           chitrangada

(2 row(s) affected)

What it does it when you use TOP 1 rows, it will return you only 1 rows, but when used TOP 1 WITH TIES, it will return you all the rows that have same value as that of the last record of TOP 1.

The expected result is based on the column that is specified in ORDER BY. That is it will look for the column used in the ORDER BY to compare its equivalent in rest of the table.

NOTE: WITH TIES Clause can be used only with TOP and ORDER BY, both the clauses are required.

Post Reference: Vikram Aristocratic Elfin Share