Determine the maximum values in several SQL Server columns

You have an n-column SQL Server table containing integers, floating-point numbers, amounts, or dates, and your task is to return the maximum value of a row in those columns. You are even asked to find the maximum number of rows at a time, and return a group of records (record set).

The first task implies that you may not know in advance which column to compare, or how many columns to compare, but this is not always the case. Maybe you know exactly which columns you want to compare, and you know it's impossible to add any new columns. On the other hand, you may have to do a preventative plan and expect to add some new columns. Writing a function that just compares n values can accomplish this task, but how useful is it in other situations?

Let's say this is your table:

CREATE TABLE [dbo]. [Minmax] (

[Minmaxid] [INT] IDENTITY (1,1) not NULL,

[Value1] [INT] Null

[Value2] [INT] Null

[Value3] [INT] Null

[Value4] [INT] Null

CONSTRAINT [Pk_minmax]primary KEY CLUSTERED

(

[Minmaxid] Asc

With (pad_index= off, ignore_dup_key = out) on [PRIMARY]

) on [PRIMARY]

Note that the table definition allows null values to be used in its four columns. In most cases, I will try to apply non-null columns, but in this case, one or several date columns are likely to contain null values; I certainly don't want to enter the wrong data just to avoid a non-null problem. So I defined all the date columns as null columns. Figure 1 shows some of the sample values you can insert into the table.

For the Simple min () and Max () functions, SQL has its own rule that compares the values in a column. Since there is no built-in function to compare any two values and determine the maximum or minimum value, you should write this function yourself. I decided to call them Min2 () and Max2 (). See List A.

You can test them with this code:

SELECT dbo. Min2 (23, 34)

SELECT dbo. MAX2 (23,null)

SELECT dbo. Min2 (NULL, 34)

SELECT dbo. MAX2 (23,null)

To compare the values in the original table, you can simply embed the function call, like this:

SELECT dbo. MAX2 (Value1, dbo. MAX2 (Value2, dbo. Max2 (Value3, Value4))

FROM dbo. Minmax

According to the original table, the result of this call is as follows:

7

10

27

81

There's still one problem: the code above converts null to 0. In the Min2 () function, this means that if the argument has a null value, the result is 0. In most cases, I think it's appropriate to do this, but in some special cases, you might want to return null instead of 0.

You may have written a function that just takes four parameters, but such a function is too special to be used in other situations. Take a moment to consider more general situations and you will get more useful functions.