Aggregate functions (like Min(), Max(), Sum(), etc.) are often used to calculate values in a SELECT statement. Instead of loading all the data and calculate the values for yourself, you let the database give you the answer. That is a lot faster and requires less typing. Aggregate functions are mostly used on rows. But did you know that SQL Server lets you use them on columns as well? This post shows how it is done what could be a simple solution for some tricky questions.
|
1 2 |
SELECT AVG(salary) FROM employee |
Nearly every tutorial on SQL comes up with this example to calculate the average salary for all employees in a company. It’s fast, easy to understand and a good use case for aggregate functions.
Let’s assume your query joins multiple tables and each one has a ModifiedDate on it:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT product.[ProductID] ,product.[Name] ,product.[ModifiedDate] ,ppp.ModifiedDate ,photo.ModifiedDate FROM [Production].[Product] product LEFT JOIN [Production].[ProductProductPhoto] ppp ON product.ProductID = ppp.ProductID LEFT JOIN [Production].[ProductPhoto] photo ON ppp.ProductPhotoID = photo.ProductPhotoID |
How do you figure out which of those 3 dates is the newest? You could write CASE WHEN statements to compare two pairs at once. A much more elegant solution is use the same aggregate function you know but on the same row using the table value constructor:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT product.[ProductID] ,product.[Name] ,(SELECT Max(v) FROM (VALUES (product.[ModifiedDate]), (ppp.ModifiedDate), (photo.ModifiedDate)) AS value(v)) AS 'ModifiedDate' FROM [Production].[Product] product LEFT JOIN [Production].[ProductProductPhoto] ppp ON product.ProductID = ppp.ProductID LEFT JOIN [Production].[ProductPhoto] photo ON ppp.ProductPhotoID = photo.ProductPhotoID |
All you need to do is to use the keyword VALUES, wrap your columns in () and separate them with a ‘,‘. The documentation isn’t that easy to understand but as the SELECT statement above shows, you can turn some specified columns into a virtual table and query them as you would with a normal one. It’s a nice trick that works with SQL Server 2008 and newer.