How to determine the maximum value in the SQL Server bar

How to determine the maximum value in the SQL Server bar

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.