Return Max (or Min) Value Across Different Columns in Result Set

Here’s a nice way to return the minimum or maximum value across multiple columns.  Obviously, it’s easy to work out the min or max value for all rows in a single column but it’s a bit trickier to find the min or max value across multiple columns for all rows.

You can use the VALUES clause in a sub-query to do this.  For example, if you have an account table with different dates for actions like first game play, first purchase etc, you can pull out the maximum date of all these account actions as follows:

CREATE TABLE dbo.AccountActions
    AccountId INT NOT NULL IDENTITY(1,1),
    FullName VARCHAR(100) NOT NULL,
    AccountOpenedDate DATETIME2(0) NOT NULL,
    FirstPurchasedDate DATETIME2(0) NULL,
    FirstGamePlayDate DATETIME2(0) NULL,
    FirstMultiPlayerDate DATETIME2(0) NULL

INSERT INTO dbo.AccountActions (FullName, AccountOpenedDate, FirstGamePlayDate, FirstMultiPlayerDate, FirstPurchasedDate)
('Adam Bennett', '20180301 17:06', '20180301 17:08', NULL, NULL),
('Charlie Dawson', '20180301 20:54', '20180301 21:32', NULL, '20180301 21:17'),
('Eric Franks', '20180301 09:41', '20180302 06:38', '20180303 20:12', NULL),
('Gina Harris', '20180303 23:19', '20180303 23:21', '20180304 06:52', '20180303 23:20');

        SELECT MAX(AllDates.TheDate)
        FROM (
            VALUES (AccountOpenedDate), (FirstGamePlayDate), (FirstMultiPlayerDate), (FirstPurchasedDate)
        ) AS AllDates (TheDate)
    ) AS MaxDate
FROM dbo.AccountActions;

Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s