Interview - Quickest Way to Get nth Maximum Value From a Table

Now-a-days  "Query to find 2nd Maximum value  or Nth Maximum value from a table" are a common questions in interviews.

Here I'm given the best one to find nth max value from a table.
Below i mentioned 3 queries. 2 takes inbuild method, 1 Without using inbuild method

USE NorthWind

GO

WITH result AS 

( SELECT DENSE_RANK() OVER(ORDER BY UnitPrice DESC) AS 'SecondMaximum',* FROM Products )

SELECT * FROM result WHERE SecondMaximum = 2;

GO

WITH result AS 

( SELECT ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS 'SecondMaximum',* FROM Products )

SELECT * FROM result WHERE SecondMaximum = 2;

GO

SELECT TOP 1 * FROM 

( SELECT TOP 2 * FROM Products ORDER BY UnitPrice DESC ) result 

ORDER BY UnitPrice ASC

the result is, the first one (DENSE_RANK) takes less time then remaining.

SecondMaximum value or Top value in subquery you can get nth maximum value

[ Hint: Here we can use "RANK" method instead of "DENSE_RANK" method, If the "UnitPrice" values are different. ]