Finding the Least and Greatest value over multiple columns.

I had a situation last week where I needed to find the MIN() and MAX() values of some data. Normally this would be pretty straightforward, but the problem was I needed to identify the MIN() and MAX() values amongst multiple columns. So today I bring you a SQL tip using APPLY.

Looking at sys.procedures we can see I have a create_date and a modify_date column for each record. What if we needed to return the MIN() or MAX() value from those 2 (or more) columns?

SELECT [Name]
,create_date
,modify_date
FROM sys.procedures AS p
WHERE p.[name] = 'ChangePassword';

SELECT

We can actually do this using CROSS APPLY.

SELECT [Name]
	,p.create_date
	,p.modify_date
	,MIN(xa.DateColumn) AS Least
	,MAX(xa.DateColumn) AS Greatest
FROM sys.procedures AS p
CROSS APPLY (
	VALUES (p.create_date)
		,(p.modify_date)
	) AS xa(DateColumn)
WHERE p.[name] = 'ChangePassword'
GROUP BY p.[name]
	,p.create_date
	,p.modify_date;

LeastGreatest

Using the APPLY operator we can supply a list of values, and that list of values can actually come from tables or views that were used earlier on. In my example, I’m querying from sys.procedures, and to make this work I’m using the columns create_date and modify_date and shoving them into a VALUES() clause which will essentially become a dataset that can be evaluated.

HowItWorks_01

Now I have a dataset named [x] that contains one column [DateColumn]. With this new dataset I’ve created, I can now use MIN() and MAX() to evaluate the values within. This will allow me to get the LEAST and GREATEST values from the CROSS APPLY.

HowItWorks_02

As always, thank you for reading!

One thought on “Finding the Least and Greatest value over multiple columns.

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 )

w

Connecting to %s