Saturday, June 19, 2010

T-SQL Query to Find Nth Largest number

How to find Nth Highest number using SQL query?

This is very simple to achieve by using Ranking Functions. Below is the answer of this query:

-- PREPARE TEST DATA
DECLARE @T TABLE (Amount int)
INSERT INTO @T VALUES
(101),(120),(14),(110),(930),(310),
(12),(104),(330),(423),(110),(10)


DECLARE @N int
SET @N = 5 -- SET Nth Number

-- ACTUAL QUERY
SELECT [Rank],Amount FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Amount DESC) [Rank]
    ,Amount FROM @T) AS Temp
WHERE [Rank] = @N

No comments:

Post a Comment