Saturday, May 12, 2012

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.

image

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.

image

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.

image

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

image

Following is the results for this.

image

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.

image

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

No comments:

Post a Comment