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.

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

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.

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

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.



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook