Select the nth best amount with simple query

In this post i would like to show you a simple code, which is used to find the nth best amount with out using any RANK or MAX funcions.

Code is simple and straight forward. You just have to pass the value to the variable @N. If you want to find the TOP amount or highest price of any given product the you need to pass value 1 to the variable @N.

This code works on AdventureWorks2008 database.

DECLARE @N smallint
SET @N = 1
Select distinct PP_o.StandardCost
from Production.Product PP_o where (@N-1) = (select count(distinct(PP_i.StandardCost))
from Production.Product PP_i
WHERE PP_o.StandardCost < PP_i.StandardCost)

Reference: www.DotNetVJ.com