December 6, 2012

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. ]

No comments:

Post a Comment

Recommended Post Slide Out For Blogger