Find nth max value in SQL Server

One of the most commonly asked interview question is to find nth max value in SQL Server. There are multiple solutions to this question.

The below query creates a dummy table to be used in below examples.

CREATE TABLE tbltest 
  ( 
     value   INT IDENTITY
  ) 
  GO
-- insert 1o rows
INSERT tbltest DEFAULT VALUES
GO 10

Find Nth maximum value using OFFSET and FETCH. This only applies to SQL SERVER 2012 and above.

GO
CREATE FUNCTION [dbo].[findNthMaxValue]
(
   @N int
)
RETURNS TABLE AS RETURN
(
    SELECT value FROM tbltest 
    ORDER BY value DESC OFFSET @N-1 ROW
    FETCH NEXT 1 ROW ONLY
)
GO

The above query creates an inline table valued functions findNthMaxValue. The query uses OFFSET and FETCH to get the result. The result set is ordered in descending order. The OFFSET @N-1 says skip @N-1 rows from the sorted result set and return the remaining rows. The FETCH NEXT 1 ROW Only says return the next 1 row. Thus to find the 3rd maximum value it will skip (@N-1 i.e. 3-1) rows which in our case are 10 and 9 (the result set is in descending order) and will then output the next 1 row i.e. 8.

The output from the function is shown below.

1_Find nth max value SQL Server

Another solution is to use a subquery get the Nth maximum value.

IF object_id('dbo.findNthMaxValue') is not null
drop function dbo.[findNthMaxValue]
GO
CREATE FUNCTION [dbo].[findNthMaxValue]
(
   @N int
)
RETURNS TABLE AS RETURN
(	
	SELECT TOP 1 value  
	FROM (	
		SELECT DISTINCT TOP(@N) value 
		FROM tbltest
		ORDER BY value DESC
	)AS sq
	ORDER BY value
)

The query is self-explanatory. The inner query gets top N values in descending order. Thus the Nth max value is the last value of the result set we get from the inner query. The outer query gets the top 1 value by ordering the result set of inner query in ascending order.

Another solution using subquery is given below

IF object_id('dbo.findNthMaxValue') is not null
drop function dbo.[findNthMaxValue]
GO
CREATE FUNCTION [dbo].[findNthMaxValue]
(
   @N int
)
RETURNS TABLE AS RETURN
(	
	SELECT *
		FROM tbltest tout
		WHERE (@N-1) = (
		SELECT COUNT(DISTINCT(tin.value))
		FROM tbltest tin
		WHERE tin.value > tout.value)
)
GO

In the above query, for each row in tout (outer table) the subquery returns number of tin.values greater than tout.values. The outer query returns the result set when the count from the subquery matches @N-1.

Do share if you are aware of any other interesting way to resolve this problem.

 

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

One Comment on “Find nth max value in SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *