How to determine the maximum value in the SQL Server Column

How to determine the maximum value in the SQL Server Column

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 = OFF) ON [PRIMARY]

) ON [PRIMARY]

Note that the table definition allows NULL values in its four columns. In most cases, I will try to apply non-NULL columns, but in this case, one or more date columns may contain NULL values; of course, I do not want to enter wrong data just to avoid non-NULL problems. So I defined all date columns as NULL columns. Figure 1 shows some sample values that you can insert into the table.

SQL has its own rule for the functions of simple Min () and Max () functions, that is, comparing values in a column. Since there is no built-in function to compare any two values and determine the maximum or minimum values, you should compile this function on your own. I decided to call them Min2 () and Max2 (). See list.

You can use this code to test them:

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 function calls like this:

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

FROM dbo. MinMax

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

7

10

27

81

There is still a problem: the above Code converts NULL to 0. In the Min2 () function, this means that if the independent variable is NULL, the return result is 0. In most cases, I think this is appropriate, but in some special cases, you may want to return NULL instead of 0.

You may have compiled a function that only accepts four parameters, but this function is too special and cannot be used in other cases. Take a moment to consider more common situations and you will get more useful functions.