Finding the Nth Maximal Value

This procedure gets a tablename, column and an integer number N as parameters
and finds the Nth maximum value of the column’s value in a table.

For example: running it with ‘products’, ‘UnitPrice’ , 13 , @res

will get the 13TH largest value of unitprice from products
is no such N exist an error message is printed.

Procedure code and usage is listed below:

=====================================


create proc max_nth_value (@tablename varchar(50),

                           @column varchar(50),

                           @n int,

                           @res decimal (10,4) OUTPUT)

AS

set nocount on

declare @sqlStatment varchar(200)

set @sqlStatment = 'select a.' + @column + ' from ' + @tableName + ' a ' +

                   'where ' + convert (varchar(10),@n) +

                   '=(select count(distinct ' + @column + ')' +

                   ' from ' + @tableName + ' b ' +

                   ' where ' + 'a.'+ @column + ' <= ' + 'b.' + @column + ')'
create table #tres (x decimal (10,4))

insert into #tres exec (@sqlStatment)

if @@rowcount = 0 print 'No value found!' else select @res = x from #tres

set nocount off

go

-- usage

declare @res decimal (10,4)

exec max_nth_value 'products','unitprice',8,@res OUTPUT

print @res

»


See All Articles by ColumnistEli Leiba