How to I modify this t-sql query to return the maximum value for different column names?

Question

I have the following query:

SELECT
        [Rate],
        [RateMon],
        [RateTue],
        [RateWed],
        [RateThu],
        [RateFri],
        [RateSat],
        [RateSun]
    FROM
        [Room]
    WHERE
        Id=@Id

Instead of returning all the columns, I just want to return Rate and the Maximum value between RateMon, RateTue, RateWed, RateThu, RateFri, RateSat, and RateSun, but I am having a hard time because the column names are different.

An example result return now is:

100, 400, 400, 400, 400, 600, 600, 600

where 100 is the Rate and the other values correspond to Mon - Sun, but I want to return just 100 and 600 in this case.

Please post example data and results. It's not clear what you are looking for. — Oded, Nov 03 '11 at 13:48
possible duplicate of [SQL MAX of multiple columns?](http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns) — Chris J, Nov 03 '11 at 13:50
possible duplicate of [Function in SQL Server 2008 similar to GREATEST in mysql?](http://stackoverflow.com/questions/4725823/function-in-sql-server-2008-similar-to-greatest-in-mysql) — Martin Smith, Nov 03 '11 at 13:54
I would not close this as an exact duplicate because Mikael Eriksson's answer provides something valuable that myself and others may have not known. — Xaisoft, Nov 03 '11 at 14:03
@Xaisoft - Did you click the duplicate link immediately above your comment? — Martin Smith, Nov 03 '11 at 14:04
Yes, I was going to use Lasse's answer, but I liked Mikaels better and I did not know you could use VALUES outside an Insert like he did in his example. This is something the other post does not have. — Xaisoft, Nov 03 '11 at 14:05
@Xaisoft - [er, it definitely has](http://stackoverflow.com/questions/4725823/function-in-sql-server-2008-similar-to-greatest-in-mysql/4725851#4725851) — Martin Smith, Nov 03 '11 at 14:06
@Michael, ah sorry about that, must have scanned too quickly. Good point. — Xaisoft, Nov 03 '11 at 14:13

score 8 · Accepted Answer · answered Nov 03 '11 at 13:55

8

SELECT  [Rate],
        (SELECT MAX(T.[Rate])
         FROM (VALUES([RateMon]),
                     ([RateTue]),
                     ([RateWed]),
                     ([RateThu]),
                     ([RateFri]),
                     ([RateSat]),
                     ([RateSun])) AS T([Rate])
        ) AS MaxRate
FROM [Room]
WHERE Id=@Id

answered Nov 03 '11 at 13:55

Mikael Eriksson

136,425
22
210
281

Best answer because it is most succint. – Xaisoft Nov 03 '11 at 14:00
Not sure if I should accept it though because you are from Sweden and I am part Norwegian :) Just kidding. – Xaisoft Nov 03 '11 at 14:01
@Xaisoft - Well, I will not hold that against you :) – Mikael Eriksson Nov 03 '11 at 14:03
1

@Andomar - Yes, this is possible since SQL Server 2008. For previous version you can use `select ... union all` instead. – Mikael Eriksson Nov 03 '11 at 14:04

Michael Goldshteyn · Answer 2 · 2011-11-03T14:03:47.273

You can use something verbose like:

SELECT Rate,
  CASE 
    WHEN RateMon>=RateTue AND RateMon>=RateWed AND RateMon>=RateThu AND 
         RateMon>=RateFri AND RAteMon>=RateSat AND RateMon>=RateSun THEN RateMon
    WHEN RateTue>=RateMon AND RateTue>=RateWed AND RateTue>=RateThu AND 
         RateTue>=RateFri AND RateTue>=RateSat AND RateTue>=RateSun THEN RateTue
    WHEN RateWed>=RateMon AND RateWed>=RateTue AND RateWed>=RateThu AND 
         RateWed>=RateFri AND RateWed>=RateSat AND RateWed>=RateSun THEN RateWed
    WHEN RateThu>=RateMon AND RateThu>=RateTue AND RateThu>=RateWed AND 
         RateThu>=RateFri AND RateThu>=RateSat AND RateThu>=RateSun THEN RateThu
    WHEN RateFri>=RateMon AND RateFri>=RateTue AND RateFri>=RateWed AND 
         RateFri>=RateThu AND RateFri>=RateSat AND RateFri>=RateSun THEN RateFri
    WHEN RateSat>=RateMon AND RateSat>=RateTue AND RateSat>=RateWed AND 
         RateSat>=RateThu AND RateSat>=RateFri AND RateSat>=RateSun THEN RateSat
    WHEN RateSun>=RateMon AND RateSun>=RateTue AND RateSun>=RateWed AND 
         RateSun>=RateThu AND RateSun>=RateFri AND RateSun>=RateSat THEN RateSun
    END AS MaxRate 
FROM    
    [Room]    
WHERE    
    Id=@Id

A lot of typing, but that's one possible answer. You can also do the following, which is slightly less typing:

SELECT Rate, MAX(Rates.Rate) AS MaxRate
  FROM    
    [Room], 
      (SELECT RateMon AS Rate FROM [Room] WHERE Id=@Id UNION
       SELECT RateTue AS Rate FROM [Room] WHERE Id=@Id UNION
       SELECT RateWed AS Rate FROM [Room] WHERE Id=@Id UNION
       SELECT RateThu AS Rate FROM [Room] WHERE Id=@Id UNION
       SELECT RateFri AS Rate FROM [Room] WHERE Id=@Id UNION
       SELECT RateSat AS Rate FROM [Room] WHERE Id=@Id UNION
       SELECT RateSun AS Rate FROM [Room] WHERE Id=@Id)
     AS Rates
  WHERE    
    Id=@Id

Of course, the correct solution would be to normalize your database and make this moot, since a simple join and aggregate would be sufficient.

Wow that is huge. Thanks, I will give it a shot. You would think there would be something more simpler like given a set of columns, return the max value instead of having to to all this case logic. — Xaisoft, Nov 03 '11 at 13:55
Yes, I read that on the other post. I wonder if it would be in a future releas of SQL Server. — Xaisoft, Nov 03 '11 at 14:04

score 1 · Answer 3 · answered Nov 03 '11 at 13:58

You could unpivot the days with a union subquery:

select  Rate
,       max(DayRate)
from    (
        select ID, Rate, RateMon as DayRate from Room
        union all
        select ID, Rate, RateTue from Room
        union all
        select ID, Rate, RateWed from Room
        union all
        ....
        ) as SubQuery
where   ID = @ID
group by
        Rate

How to I modify this t-sql query to return the maximum value for different column names?

3 Answers3

Linked