Post reply

x-way min

  • 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.

  • July 18, 2007 at 3:49 am

    #720308

    no, you will have to develop one for your self

    Everything you can imagine is real.

  • 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"

  • 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)
     
     

  • 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

  • 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

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply