Thursday, November 11, 2010

Retrieve Nth Highest Value

I often ask this question during interviews to test candidates and even some experienced persons are unable to give proper answer. Here's the optimized query i decided to share:

SELECT section_id
FROM
(SELECT DISTINCT section_id
, ROW_NUMBER() OVER (ORDER BY section_id DESC) AS rnk
FROM Sections_Detail
WHERE section_id IS NOT NULL) AS s
WHERE rnk = n ;

Where n could be any value to retrieve. Thanks!

No comments:

Post a Comment