CODEWITHSUNDEEP
sqlsql-server-2012
whytheq
whytheq

Reputation: 35557

Max of several columns

I have a scenario similar to the following and all I'm want to do is find the max of three columns - this seems like a very long-winded method that I'm using to find the column mx.

What is a shorter more elegant solution? 

CREATE TABLE #Pig
(
   PigName CHAR(1),
   PigEarlyAge INT,
   PigMiddleAge INT,
   PigOldAge INT
)

INSERT INTO #Pig VALUES
('x',5,2,3),
('y',2,9,5),
('z',1,1,8);

WITH Mx_cte
    AS
    (
    SELECT  PigName,
            Age = PigEarlyAge 
    FROM    #Pig
    UNION
    SELECT  PigName,
            Age = PigMiddleAge 
    FROM    #Pig
    UNION
    SELECT  PigName,
            Age = PigOldAge 
    FROM    #Pig
    )
SELECT  x.PigName,
        x.PigEarlyAge,
        x.PigMiddleAge,
        x.PigOldAge,
        y.mx 
FROM    #Pig x
        INNER JOIN 
        (
        SELECT  PigName,
                mx = Max(Age) 
        FROM    Mx_cte
        GROUP BY PigName
        ) y
        ON
            x.PigName = y.PigName

Upvotes: 2

Views: 176

Answers (3)

Martin Smith
Martin Smith

Reputation: 453288

SQL Server has no equivalent of the GREATEST function in other RDBMSs that accepts a list of values and returns the largest.

However you can simulate something similar by using a table valued constructor consisting of the desired columns then applying MAX to that. 

SELECT *,
       (SELECT MAX(Age)
        FROM   (VALUES(PigEarlyAge),
                      (PigMiddleAge),
                      (PigOldAge)) V(Age)) AS mx
FROM   #Pig

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you want the max of the three fields:

 select (case when max(PigEarlyAge) >= max(PigMiddleAge) and max(PigEarlyAge) >= max(PigOldAge)
              then max(PigEarlyAge)
              when max(PigMiddleAge) >= max(PigOldAge)
              then max(PigMiddleAge)
              else max(PigOldAge)
         end)
  from #Pig

If you are looking for the rows that have the respective maxima, then use row_number() along with the union:

select p.PigName, PigEarlyAge, PigMiddleAge, PigOldAge,
       (case when PigEarlyAge >= PigMiddleAge and PigEarlyAge >= PigOldAge then PigEarlyAge 
             when PigMiddleAge >= PigOldAge then PigMiddleAge 
             else PigOldAge 
         end) as BigAge
from (select p.*,
             row_number() over 
                   (order by (case when PigEarlyAge >= PigMiddleAge and PigEarlyAge >= PigOldAge then PigEarlyAge 
                                   when PigMiddleAge >= PigOldAge then PigMiddleAge 
                                   else PigOldAge 
                              end) desc
                   ) seqnum as seqnum
      from #Pig p
     ) p
where p.seqnum = 1;

If you want duplicate values, then use rank() instead of row_number().

Upvotes: 1

suff trek
suff trek

Reputation: 39777

If you need combined max of ages, you can simple add them inside of the Max function:

SELECT PigName, MAX(PigEarlyAge + PigMiddleAge + PigOldAge) as MaxAge
FROM  #Pig
GROUP BY PigName

Upvotes: 0

Related Questions