Sql: Use Of Ranking Function rank()

Ranking 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

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:

• 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.