|
Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the creation and testing of a function (UDF) to return the maximum of two numbers and MAX function usage with GROUP BY.
-- SQL maximum of two numbers - money data type
USE AdventureWorks2008;
GO
-- T-SQL user-defined function - scalar function
-- SQL calculate the maximum of two values - mssql max function
CREATE FUNCTION fnMaxValue
(@ColumnA MONEY,
@ColumnB MONEY)
RETURNS MONEY
AS
BEGIN
RETURN (0.5 * ((@ColumnA + @ColumnB) + abs(@ColumnA - @ColumnB)))
END
GO
-- Test user-defined function - UDF
SELECT dbo.fnMaxValue (10.0, 10.0) -- 10.00
SELECT dbo.fnMaxValue (10.0, 12.0) -- 12.00
SELECT dbo.fnMaxValue (12.0, 10.0) -- 12.00
SELECT dbo.fnMaxValue (10.001, 10.002) -- 10.002
SELECT dbo.fnMaxValue (-12.0, -10.0) -- -10.00
GO
-- Select the highest amount of tax and freight
SELECT TOP ( 10 ) SalesOrderID,
TaxAmt,
Freight,
Maximum = dbo.fnMaxValue(TaxAmt, Freight)
FROM Sales.SalesOrderHeader
ORDER BY NEWID()
GO
/* Results
SalesOrderID TaxAmt Freight Maximum
74397 7.1976 2.2493 7.1976
51956 1.9024 0.5945 1.9024
44152 271.9992 84.9998 271.9992
67058 5.9984 1.8745 5.9984
69341 0.3992 0.1248 0.3992
61930 0.5824 0.182 0.5824
60116 138.8784 43.3995 138.8784
56853 5.4344 1.6983 5.4344
54184 43.1992 13.4998 43.1992
51704 2668.6276 833.9461 2668.6276 */
------------
-- Maximum values by group
SELECT Color=coalesce(Color,'N/A'), MaxListPrice=MAX(ListPrice)
FROM AdventureWorks2008.Production.Product
GROUP BY Color
/* Color MaxListPrice
N/A 229.49
Black 3374.99
Blue 2384.07
Grey 125.00
Multi 89.99
Red 3578.27
Silver 3399.99
Silver/Black 80.99
White 9.50
Yellow 2384.07 */
------------
Related articles:
http://sqlusa.com/bestpractices/minimum/
T-SQL equivalent of Excel “MAX” function to return larger of two numbers
|
