Finding Nth highest number in SQL Server

Many times you face this question while working with real time data and most interviews. In this post we will go through different ways to find the records with Nth highest number. Use this script to populate some sample data to test the results.

CREATE TABLE PLAYER
(
PLAYERID INT IDENTITY(100,2) NOT NULL,
NAME VARCHAR(100) NOT NULL,
DOB DATETIME,
COUNTRY VARCHAR(3),
PRIZEMONEY MONEY NOT NULL
)
GO

INSERT INTO PLAYER(NAME,DOB,COUNTRY,PRIZEMONEY) VALUES 
('Andre Agassi','4/29/1970','USA',$31152975),  ('Rafael Nadal','6/3/1986','ESP',$27224163), ('Boris Becker','11/22/1967','GER',$25080956),  
('Yevgeny Kafelnikov','2/18/1974','RUS',$23883797), ('Ivan Lendl','3/7/1960','USA',$21262417),  ('Stefan Edberg','1/19/1966','SWE',$20630941),
('Goran Ivanisevic','9/13/1971','CRO',$19876579),  ('Michael Chang','2/22/1972','USA',$19145632), ('Lleyton Hewitt','2/24/1981','AUS',$18312036), 
('Andy Roddick','8/30/1982','USA',$17109084), ('Novak Djokovic','5/22/1987','SRB',$15984098), ('Gustavo Kuerten','9/10/1976','BRA',$14807000),
('Jonas Bjorkman','3/23/1972','SWE',$14600323),  ('Marat Safin','1/27/1980','RUS',$14373291), ('Jim Courier','8/17/1970','USA',$14034132),  
('Carlos Moya','8/27/1976','ESP',$13382822), ('Nikolay Davydenko','6/2/1981','RUS',$1339499),  ('Michael Stich','10/18/1968','GER',$129528),
('Juan Carlos Ferrero','2/12/1980','ESP',$12588898),  ('John McEnroe','2/16/1959','USA',$12552132), ('Thomas Muster','10/2/1967','AUT',$12225910),  
('Tim Henman','9/6/1974','GBR',$11635542), ('Sergio Bruguera','1/16/1971','ESP',$11632199),  ('Patrick Rafter','12/28/1972','AUS',$11127058),
('Thomas Enqvist','3/13/1974','SWE',$10461641),  ('Petr Korda','1/23/1968','CZE',$10448900), ('Alex Corretja','4/11/1974','ESP',$10411354),  
('Todd Woodbridge','4/2/1971','AUS',$10095245), ('Richard Krajicek','12/6/1971','NED',$10077425), ('Roger Federer','8/8/1981','SUI',$53362068),  
('Pete Sampras','8/12/1971','USA',$43280489)
GO

Here is 4 different ways to find the same result.

--Define Level of N
DECLARE @N INT = 3

--Method-1: Without using any functions
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT DISTINCT PRIZEMONEY FROM PLAYER p1 WHERE (@N-1)=
(SELECT COUNT(DISTINCT p2.PRIZEMONEY) FROM PLAYER p2 WHERE p2.PRIZEMONEY > p1.PRIZEMONEY ))

--Method-2: Using TOP
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT TOP 1 PRIZEMONEY FROM PLAYER
WHERE PRIZEMONEY in (SELECT DISTINCT TOP (@N) PRIZEMONEY FROM PLAYER ORDER BY PRIZEMONEY DESC)
ORDER BY PRIZEMONEY)

--Method-3: Using MIN() function
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT MIN([t].[amt]) FROM
(SELECT DISTINCT TOP (@N) PRIZEMONEY AS [amt] FROM PLAYER ORDER BY PRIZEMONEY DESC) AS [t])

--Method-4: Using Dense_Rank()
;WITH cte
AS (
SELECT NAME,DOB,COUNTRY,PRIZEMONEY,  DENSE_RANK() OVER (ORDER BY PRIZEMONEY DESC) AS [rnk]
FROM PLAYER t
)
SELECT NAME,DOB,COUNTRY,PRIZEMONEY
FROM cte
WHERE [rnk] = @N

Also let me know your suggestions on this.

Generate Report with Alternate Order of an Attribute (say Gender) in SQL Server

Some times we may need to generate a report which display employees/customers in alternate order of any particular attribute.This post describes for employees with alternate order of Gender i.e. first a Male employee then a Female employee and so on or vice versa.

In SQL Server this is just a simpler way. There could be may ways to do this. Here is my set based approach with ROW_NUMBER() function.

DECLARE @EMPLOYEE TABLE
(
NAME VARCHAR(100) NOT NULL,
GENDER VARCHAR(1) NOT NULL
)

--Populate sample data
INSERT INTO @EMPLOYEE(NAME,GENDER) VALUES
('Anup','M'), ('Sheetal','F')
,('Sonam','F'), ('Payal','F')
,('Parul','F'), ('Peter','M')
,('Rahul','M'), ('Roxy','F')
,('Preeti','F'), ('Manav','M')

SELECT * FROM @EMPLOYEE

--Result
;WITH CTE(SEQ,ENAME,GENDER)
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY GENDER ORDER BY GENDER), NAME, GENDER FROM @EMPLOYEE
)
SELECT ENAME,GENDER FROM CTE
ORDER BY SEQ, GENDER DESC
GO
 

Also put your valuable suggestions on this.