TIES vs RANK

Let us come with this scenario. I want to display student who has got highest marked.

Let us say my data set is similar to following.

You might not think this is a hard question and you will say you can go with TOP 1 with DESC for the Value column as shown in the below query.

However, the problem is what if there are more than one top scored students in the list as above will return only record.

To solve the above problem, you can go with RANK function as shown bellow.

However, in TOP syntax there is a feature called WITH TIES to address the above issue.

Following is the results for this.

You can see that though you specified TOP 1, it returns you all the records which has highest marks.

Let us analysis the query plan for the both queries. First is the query with WITH TIES and the second is with RANK function.

So, WITH TIES query is preforming better to the RANK.