x-way min

• Carl Rimmer

  SSCarpal Tunnel

  Points: 4068

  More actions

  July 18, 2007 at 3:25 am

  #175411

  Is there a built in SQL function that can return either the min or the max of 2 or more arguments/columns and I don't mean the aggregate min and max functions. Min across a number of columns of a row.

  i.e. min(col3, col4, col5) for every row in a table.

• bledu

  SSChampion

  Points: 12335

  More actions

  July 18, 2007 at 3:49 am

  #720308

  no, you will have to develop one for your self

  ---

  Everything you can imagine is real.
  

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  July 18, 2007 at 12:12 pm

  #720484

  Or

  SELECT PkCol, Min(Col), Max(Col)

  FROM (

  SELECT PkCol, Col1 AS Col FROM Table1 UNION ALL

  SELECT PkCol, Col2 FROM Table1 UNION ALL

  SELECT PkCol, Col3 FROM Table1

  ) AS d

  GROUP BY PkCol

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  July 18, 2007 at 3:43 pm

  #720566

  Here is a way to do it with a subquery.  It may be handier to do it this way, because it does not require the main query to be grouped.  It should be fast, because it requires only a single scan of the table.

  create table #t (
  n1 int not null,
  n2 int not null, 
  n3 int not null, 
  n4 int not null, )
  go
  -- load test data
  insert into #t
  select top 10
   convert(int,convert(varbinary(4),newid())),
   convert(int,convert(varbinary(4),newid())),
   convert(int,convert(varbinary(4),newid())),
   convert(int,convert(varbinary(4),newid()))
  from
   sysobjects
  go

  select
   [MIN_of_N1_to_N4] = 
    ( 
    select
     X1= min(bb.xx)
    from
     (
     select xx = a.n1 union all
     select xx = a.n2 union all
     select xx = a.n3 union all
     select xx = a.n4
     ) bb ),
   [MAX_of_N1_to_N4] = 
    ( 
    select
     X1= max(bb.xx)
    from
     (
     select xx = a.n1 union all
     select xx = a.n2 union all
     select xx = a.n3 union all
     select xx = a.n4
     ) bb ),
  a.n1,
  a.n2,
   a.n3,
   a.n4
  from
   #t a

  go
  drop table #t

  Results:

  (10 row(s) affected)

  MIN_of_N1_to_N4 MAX_of_N1_to_N4 n1          n2          n3          n4          
  --------------- --------------- ----------- ----------- ----------- ----------- 
  -1125377721     1857210973      -336343153  1857210973  -1125377721 360856562
  -920621216      1046479340      -920621216  -687533484  740128811   1046479340
  -1418749343     2127492547      -1418749343 -65306331   2127492547  1635110863
  -2041077584     1912777067      -2041077584 -1644867532 1497975561  1912777067
  -1403933386     1759089534      1725343220  -1403933386 1759089534  100005123
  -1405537398     695666346       -1195810742 624555110   695666346   -1405537398
  -1310192151     1617335431      893226343   1030635194  -1310192151 1617335431
  -1251417061     2067539264      1822021199  -1204485671 2067539264  -1251417061
  -1449145944     2063959997      -1449145944 -423672719  2063959997  412544027
  -1448602417     2063061612      1407831686  2063061612  -1261766763 -1448602417

  (10 row(s) affected)

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  July 20, 2007 at 5:41 am

  #721061

  Using the CASE seems simplier and a comparison of the executions plans between the CASE versus sub-query shows a significant difference that would affect performance.

  select  CASE 

   WHEN n1 < n2 and n1 < n3 and n1 < n4 then n1

   WHEN n2 < n1 and n2 < n3 and n2 < n4 then n2

   WHEN n3 < n1 and n3 < n2 and n3 < n4 then n3

   WHEN n4 < n1 and n4 < n2 and n4 < n3 then n4

   END as NMin

  , CASE

   WHEN n1 > n2 and n1 > n3 and n1 > n4 then n1

   WHEN n2 > n1 and n2 > n3 and n2 > n4 then n2

   WHEN n3 > n1 and n3 > n2 and n3 > n4 then n3

   WHEN n4 > n1 and n4 > n2 and n4 > n3 then n4

   END as NMax

  , n1, n2, n3, n4

  from  #t

  SQL = Scarcely Qualifies as a Language

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  July 20, 2007 at 8:39 am

  #721161

  Your case statements produce null results when the values are equal.  Even if you correct the logic, it is more difficult to scale it up for more columns (x-way min), while the subquery just needs one more line in the subquery.

  When I looked at the query plan, my version with the subquery showed that the table scan was 97% of the cost, while your query showed the table scan as 100% of the cost, so the additional overhead for the subquery looks like only about 3%.

  select  CASE 
   WHEN n1 < n2 and n1 < n3 and n1 < n4 then n1
   WHEN n2 < n1 and n2 < n3 and n2 < n4 then n2
   WHEN n3 < n1 and n3 < n2 and n3 < n4 then n3
   WHEN n4 < n1 and n4 < n2 and n4 < n3 then n4
   END as NMin
  , CASE
   WHEN n1 > n2 and n1 > n3 and n1 > n4 then n1
   WHEN n2 > n1 and n2 > n3 and n2 > n4 then n2
   WHEN n3 > n1 and n3 > n2 and n3 > n4 then n3
   WHEN n4 > n1 and n4 > n2 and n4 > n3 then n4
   END as NMax
  , n1, n2, n3, n4
  from ( select n1=1, n2=1, n3=1, n4 =1 ) #t

  Results:
  NMin        NMax        n1          n2          n3          n4          
  ----------- ----------- ----------- ----------- ----------- ----------- 
  NULL        NULL        1           1           1           1