SQL Server built-in function MIN() is an aggregate function. It can find minimal value on a single column from many rows.
To find minimum of multiple columns you can write UDF or use a subquery.
Important! My examples works fine even with nullable values, i.e. they ignore null values and return a minimum only from concrete values. If all values are null, they return null.
1. Scalar function for two values
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE FUNCTION [dbo].[Min2] ( @a int, @b int ) RETURNS int AS BEGIN RETURN CASE WHEN @a < @b THEN @a ELSE ISNULL(@b, @a) END END GO |
2. Scalar function for three values
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE FUNCTION [dbo].[Min3] ( @a int, @b int, @c int ) RETURNS INT AS BEGIN RETURN CASE WHEN @a < @b THEN CASE WHEN @a < @c THEN @a ELSE COALESCE(@c, @a) END WHEN @b < @c THEN @b ELSE CASE WHEN @a < @c THEN @a ELSE COALESCE(@c, @b, @a) END END END GO |
It can be quite hard to write a function for more input values. You can use a subquery here.
|
1 2 3 4 5 |
DECLARE @a int = 1, @b int = 2, @c int = null; SELECT 'Subquery', MinValue = (SELECT MIN(x) FROM (VALUES (@a), (@b), (@c)) AS n(x)); |