Multi-Column “Aggregation” MAX(), MIN(), COUNT() & Co with #SQLServer

It’s amazing to see how Microsoft keep on improving T-SQL. I was happily surprised to hear that GREATEST and LEAST are comming to Azure SQL Database from Version 2016 on… however only on Azure SQL.

I’m currently ‘stuck’ with SqlServer 2017 on Premise (at least it is not any out of support version, like 2008R2) so here is my goto for GREATEST/LEAST with post-hoc queries for SQL Server on Premise:

select 
    m.DiagnoseId, m.TumorHeight, m.TumorWidth, m.TumorLength,
    (select max(s) 
     from (values (m.TumorHeight), 
                  (m.TumorLength), 
                  (m.TumorWidth)
    ) as x(s)) as Size
from 
    #matmax as m
;

It may not be the most performant, but I find it elegant and clear. It conveys the intention in, and only in, the SELECT-clause. No need to jump forth and back as it is the case with a OUTER APPLY or a CTE version.

That was a small and simple post, and nothing new but I considered it worth sharing to highlight the power of VALUES() one more time.

(And this is the setup for the query)

drop table if exists #matmax
GO

create table #matmax 
    (
        DiagnoseId  int identity 
            primary key clustered, 
        TumorHeight int null, 
        TumorLength int null, 
        TumorWidth  int null
    );
GO

insert into #matmax (TumorHeight, TumorLength, TumorWidth)
    Values 
        (1, 3, 3),
        (NULL, NULL, NULL),
        (5, NULL, 4),
        (20, 0, 50);

Personal update: I’m sailing off to new horizons and may have less to blog on SQL Server in particular. New position and new RDBMS: I’ll get acquainted with DB2… let’s see how this blog evolves, but SQL should stay my main focus.