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.