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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s