How To Get Greatest And Least Values In A SQL Server Query

Update: Azure SQL DB And SQL Server 2022 Will Have These

---

So be on the lookout.

Spinning Out Of Control

---

It’s sorta kinda pretty crazy when every major database platform has something implemented, and SQL Server doesn’t.

Geez, even MySQL.

But a fairly common need in databases is to find the max value from two columns.

Maybe even across two tables.

Track V

---

For one table, it’s fairly straight forward.

SELECT     MAX(x.CombinedDate) AS greatest
FROM       dbo.Users AS u
CROSS APPLY( VALUES( u.CreationDate ), ( u.LastAccessDate )) AS x( CombinedDate );

We’re using our old friend cross apply with a values clause to create on “virtual” column from two date columns.

As far as indexing goes, I couldn’t find any performance difference between these two. They both take about 1 second.

CREATE INDEX smoochies ON dbo.Users(CreationDate, LastAccessDate);
CREATE INDEX woochies ON dbo.Users(LastAccessDate, CreationDate);

Indexing strategy will likely rely on other local factors, like any where clause filtering.

Monolith

---

A similar pattern will work across two tables:

SELECT     MAX(x.Score)
FROM       dbo.Posts AS p
JOIN       dbo.Comments AS c
    ON p.Id = c.PostId
CROSS APPLY( VALUES( p.Score ), ( c.Score )) AS x ( Score );

Though this is the worst possible way to write the query. It runs for around 10 seconds.

The indexes I have for this query look like so:

CREATE INDEX thicc ON dbo.Posts(Id, Score);
CREATE INDEX milky ON dbo.Comments(PostId, Score);

Reversing the key column order helps — the query runs in about 3 seconds, but I need to force index usage.

Of course, this is still the second worst way to write this query.

The best way I’ve found to express this query looks like so:

SELECT MAX(x.Score)
FROM
    (
     SELECT MAX(p.Score) AS Score
     FROM dbo.Posts AS p
    ) AS p
CROSS JOIN
    (
     SELECT MAX(c.Score) AS Score
     FROM dbo.Comments AS c
    ) AS c
CROSS APPLY( VALUES( p.Score ), ( c.Score )) AS x( Score );

The cross join here isn’t harmful because we only produce two rows.

And it finishes before we have time to move the mouse.

Likewise, the faster pattern for a single table looks like this:

SELECT MAX(x.Dates)
FROM
    (
     SELECT MAX(u.CreationDate) CreationDate
     FROM dbo.Users AS u
    ) AS uc
CROSS JOIN
    (
     SELECT MAX(u.LastAccessDate) LastAccessDate
     FROM dbo.Users AS u
    ) AS ul
CROSS APPLY (VALUES (uc.CreationDate), (ul.LastAccessDate)) AS x (Dates);

Because we’re able to index for each MAX

CREATE INDEX smoochies ON dbo.Users(CreationDate);
CREATE INDEX woochies ON dbo.Users(LastAccessDate);

Of course, not every query can be written like this, or indexed for perfectly, but it’s gruel for thought if you need specific queries like this to be as fast as possible.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.