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 Columnist
Eli Leiba

Eli Leiba
Eli Leiba
Eli works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 11 years experience working in the database field. Additionally, Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies.

Latest Articles