SQL Server Performance Forum – Threads Archive

SP for getting the Nth highest value of a column

Create procedure dbo.NthHighest
(
@Number integer,
@FieldNamevarchar(100),
@TableNamevarchar(100)
)
as
declare @SQL varchar(1000) set @SQL = ‘select min(‘ + @FieldName + ‘)’
set @SQL = @SQL + ‘ from ‘ + @TableName
set @SQL = @SQL + ‘ where ‘ + @FieldName
set @SQL = @SQL + ‘in ( select top ‘ + convert(varchar, @Number) + ‘ ‘ + @FieldName
set @SQL = @SQL + ‘from ‘ + @TableName
set @SQL = @SQL + ‘where ‘ + @FieldName + ‘ < ( select max(‘ + @FieldName
set @SQL = @SQL + ‘ ) from ‘ + @TableName + ‘) ‘
set @SQL = @SQL + ‘order by ‘ + @FieldName + ‘ desc )’ exec (@SQL)

---

Well. Hereafter post your scripts at "Contribute your SQL Scripts" Section
Madhivanan Failing to plan is Planning to fail

---

I agree with Madhivanan. Put this in the right section here. Also, there is no need for dynamic SQL.http://www.sql-server-performance.com/q&a124.asp
—
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

]]>