This article describes how to get the Nth maximal (or minimal) value of any given column name from any table. The column should be of a primitive type (numbers, strings or dates), not one of data type text or image.

The SQL language supplies us with the MIN, MAX and AVG system functions as aggregation functions that give us information about special extreme values in tables. If we want to determine, for example, what is the second, third, Nth minimal, or maximal values, we must write some code. T-SQL does not offer a function to give single values.

In this article, I suggest two ways of achieving such a function.

First, you must create a table in the master database (I called it [Values]) in order to hold the parameter column intermediate values while the the functions execute. This is necessary since user-defined function code requires a query from a physical existing table and does not allow the use of temporary tables.

USE master
GO
Create Table [Values] (val sql_variant)

In order to make the function public for all databases, you must enter the function into the system’s schema to allow updates on the system tables. You can accomplish this by executing the following:

USE master
GO
-- allow system updates in server
EXEC sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

That way the functions can be accessed from every database on the server.

The First Method for Getting the Nth Maximal Value

The first function, fn_max , will compute the Nth maximal values by inserting all of the column values into the [Values] table. Then, by opening a dynamic scrollable cursor, an absolute fetch is done to the Nth position, on the cursor, to fetch the result. Note that by using xp_cmdshell, I bypassed the limitation of doing an insert statement inside a scalar user-defined function code. Only functions and extended stored procedures can be called from inside a user-defined function code. In order to execute an updating statement, only an extended procedure can be used. The simplest way do to it is through use of xp_cmdshell that runs osql.

Here’s the function code for the first method:

CREATE FUNCTION system_function_schema.fn_max
                (@table_name  sysname,
                 @column_name sysname,
                 @n            int)
returns sql_variant 
as
begin

   Declare @c cursor
   Declare @sqlStmt varchar(200)
   Declare @retVal sql_variant

    set @retVal = NULL
    exec master..xp_cmdShell
                'osql -E -Q "truncate table master..[Values]"'
    set @sqlStmt = 'insert master..[Values] select ' +
                 @column_name + ' FROM ' + db_name() + '..' +
        @table_name

    set @sqlStmt = 'osql -E -Q ' + '"' + @sqlStmt + '"'

    exec master..xp_cmdShell  @sqlStmt

    set @c = CURSOR SCROLL DYNAMIC
                FOR select * from master..[Values]
                 order by 1 desc
    open @c
    fetch absolute @n from @c into @retval
    close @c
    deallocate @c

    return @retval 
 end
go

The Second Method

In the second method, I coded fn_max2. I do not use a cursor, instead I use a SELECT TOP statement combined with the INSERT statement that populates the [Values] table. That brings us the TOP N maximal values. Then I use the MIN function to get the minimal value for that set, which is the overall Nth maximal value in the table. Here’s the function code for the second method:

CREATE FUNCTION system_function_schema.fn_max2
                (@table_name  sysname,
                 @column_name sysname,
                 @n            int)
returns sql_variant
as
begin

   Declare @sqlStmt varchar(200)
   Declare @retVal sql_variant

    set @retVal = NULL
    exec master..xp_cmdShell
                'osql -E -Q "truncate table master..[Values]"'
    set @sqlStmt = 'insert master..[Values] select top ' +
                 convert (varchar(20),@n) + ' ' + 
                 @column_name + ' FROM ' + db_name() + '..' + 
                 @table_name + ' order by 1 desc '

    set @sqlStmt = 'osql -E -Q ' + '"' + @sqlStmt + '"'
    exec master..xp_cmdShell  @sqlStmt
    select @retval = min (val) from master..[Values]
    return @retval
 end 
go

Also, do not forget to disallow updates on system tables after creating both functions by using:

EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE 
GO 

Here is an example of a call to the functions:

Use Northwind
go
select fn_max ('Products','UnitPrice',2) as secondExpensiveProduct,
       fn_max2 ('Products','UnitPrice',2) as secondExpensiveProductM2,
       fn_max ('Products','ProductId',4) as FourthMaxProductId,
       fn_max2 ('Products','ProductId',4) as FourthMaxProductIdM2,
       fn_max ('Employees','LastName+FirstName' ,5) FifthMaxEmpName,
       fn_max2 ('Employees','LastName+FirstName' ,5) FifthMaxEmpNameM2

Conclusion

This article shows two methods for getting the Nth maximal value (getting the Nth minimal is really the same, but with a reversed sort order in both cases). These functions can be entered to the system's schema and serve us in cases where we want to know not only the minimal or maximal values, but also values that are near them.

--

Eli Leiba 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 13 years’ experience working in the databases 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. You can contact him at iecdba@hotmail.com.