SQL Server Performance

SP for getting the Nth highest value of a column

Discussion in 'T-SQL Performance Tuning for Developers' started by manu_k999, Jun 7, 2005.

  1. manu_k999 New Member

    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)
  2. Madhivanan Moderator

    Well. Hereafter post your scripts at "Contribute your SQL Scripts" Section


    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

Share This Page