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.