Simulating MAX and MIN functions in T-SQL

Just another SQL quickie that I know will come in handy in the future: T-SQL’s MAX and MIN functions only work on row sets returned in a query. It does not have a way to evaluate a set of values and return the highest or lowest value.

You can either create a set of IFs to do this (yech!) or a function (clutter!).

Or…

SELECT MAX(a) FROM (SELECT 7 a 
                     UNION SELECT 3 
                     UNION SELECT 16 
                     UNION SELECT 12) AS dummy;
SELECT MIN(a) FROM (SELECT 7 a 
                     UNION SELECT 3 
                     UNION SELECT 16 
                     UNION SELECT 12) AS dummy;

It may not be the fastest way to do it, but, depending on the circumstances, it could be a reasonably clean and understandable method.