Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL Scripts & Samples

Posted Jul 22, 2002

Finding the Nth Maximal Value

By Eli Leiba

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)
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

-- usage
declare @res decimal (10,4)
exec max_nth_value 'products','unitprice',8,@res OUTPUT
print @res

» See All Articles by Columnist Eli Leiba

SQL Scripts & Samples Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM

Thanks for your registration, follow us on our social networks to keep up-to-date