Suppose you want to find nth highest salary from a table,Then
you can use DENSE_RANK() function.Here i show some example that show results
and i hope clear concept about ranking functions
DENSE_RANK(),RANK(),ROW_NUMBER(),NTILE()
in SQL Server:
CREATE TABLE [dbo].[Person1](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](20) NULL,
[sal] [int] NULL
) ON [PRIMARY]
Insert Data into table
insert into Person1 (name,sal)values('Anil','100000')
insert into Person1 (name,sal)values('Patel','100000')
insert into Person1 (name,sal)values('Chikoti','300000')
insert into Person1 (name,sal)values('Nikol','100000')
insert into Person1 (name,sal)values('Rajeev','100000')
insert into Person1 (name,sal)values('Binod','300000')
insert into Person1 (name,sal)values('Pradeep','500000')
insert into Person1 (name,sal)values('Dinel','300000')
insert into Person1 (name,sal)values('Gemil','400000')
Brief about These: check MSDN
· ROW_NUMBER():Returns
rownumber of each row of result set.
Syntax:
ROW_NUMBER ( )
OVER ( [ PARTITION BY
value_expression , ... [ n ] ] order_by_clause )
· DENSE_RANK(): Returns the
rank of rows within the partition of a result set, without any gaps in the
ranking.
Syntax:
DENSE_RANK ( ) OVER( [ <partition_by_clause> ] < order_by_clause >
)
· RANK():
Returns
the rank of each row within the partition of aresult set.
Syntax:
RANK ( )
OVER ( [
partition_by_clause ] order_by_clause )
Now see result
select
id,
name,
sal,
DENSE_RANK() over (order by sal) as [DENSE_RANK],
RANK() over (order by sal) as [RANK()],
ROW_NUMBER() over (order by sal) as [ROW_NUMBER()]
from Person1
OUTPUT:
Now if we want to find name that gets 2nd highest salary then execute this
query
DENSE_RANK() can be
used for this purpose because it provide a number for same values.
SELECT
a.Name,a.sal
FROM(SELECT Name,sal,DENSE_RANK() over(ORDER BY sal) AS rk FROM Person1) as a where rk=2
If you want to 3rd highest then put rk=3 and as you wish.
SELECT
a.Name,a.sal
FROM(SELECT Name,sal,DENSE_RANK() over(ORDER BY sal) AS rk FROM Person1) as a where rk=3
