Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

Find Nth highest salary using RANKING functions


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 a result 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


Comments

Leave a comment on the original post [queryingsql.blogspot.com, opens in a new window]

Loading comments...