datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to find the maximum of two numbers?

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
 
 

Exam Prep 70-461
Exam 70-461