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.
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
We can use DENSE_RANK() Window function to get the Nth max value in most efficient way