Technical Article

Find Nth Maximum from a Table...

,

This is very simple query to find Nth Maximum number from a Table. For example, using this query, we can identify the 5th, 8th or Nth highest paid employee of an organization.

/*=========================================================
Created By:Hari Narayan Sharma
Software Engineer, Harbinger TechAxes Pvt. Ltd.
Mobile: 09214444048
=========================================================*/DECLARE @Table Table 
(ID int, Name varchar(100), Salary int)
INSERT INTO @Table (ID,Name,Salary) Values (1,'John',12000)
INSERT INTO @Table (ID,Name,Salary) Values (2,'Shan',8000)
INSERT INTO @Table (ID,Name,Salary) Values (3,'Ravi',9000)
INSERT INTO @Table (ID,Name,Salary) Values (4,'Tom',14000)
INSERT INTO @Table (ID,Name,Salary) Values (5,'Hari',17000)
INSERT INTO @Table (ID,Name,Salary) Values (6,'Dick',10000)
INSERT INTO @Table (ID,Name,Salary) Values (7,'Tina',20000)
INSERT INTO @Table (ID,Name,Salary) Values (8,'Ram',19000)
INSERT INTO @Table (ID,Name,Salary) Values (9,'Raj',6000)
INSERT INTO @Table (ID,Name,Salary) Values (10,'Ajay',8500)

--First Method [For SQL Server 2005 Only]--
SELECT * From (SELECT  *,(Dense_Rank() Over (Order By Salary Desc)) As Rank 
From @Table) As Z Where Rank=3

--Second Method [For All Versions]--
SELECT * From (
SELECT  *,(Select Count(Salary) From @Table Where Salary>=T.Salary) As Rank 
From @Table As T)  As ZWhere Rank=3

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating