Wednesday 20 February 2013

Sql: Use Of Ranking Function rank()

Kailash ChandelRanking functions are used with the over clause.
Before we continue to work with the ranking functions we must have knowledge of the partition.by

 rank():
Returns the rank of each row within the partition of a result set.
The syntax for using rank functions is

select * from(select *, rank() over(partition by <fieldname>order by <fieldname> asc) as ranking from table_name)testing where ranking<3

Above query will return two records for each group with the all fields.

To understand the use of rank function let us take the an example:-
                Let us have a table named sampleForRankingFunctions having the following records


Use of ranking functions rank()
Now we want to show first two records of each group then we can write the query as:


The output of the query will be:
Use of ranking functions rank()

  • rank() returns a rank of each row within the partition of a result set. Another example of the rank use is when you want to show top n records having two records at final position, now question is that which record will be displayed out of the two records. rank() will rank the rows of  result set and show the record at last according the condition of rank.










3 comments:

How to get code from a published ASP.Net build where we don't have source code?

If you have ever lost or misplaced your source code for an ASP.Net web application, you might wonder if there is a way to recover it from th...