Function in SQL Server 2008 Similar to Greatest in MySQL

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

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

What is the MS SQL Server capability similar to the MySQL FIELD() function?

Use a CASE expression (SQL Server 2005+):

ORDER BY CASE status
           WHEN 'active' THEN 1
           WHEN 'approved' THEN 2
           WHEN 'rejected' THEN 3
           WHEN 'submitted' THEN 4
           ELSE 5
         END

You can use this syntax for more complex evaluation (including combinations, or if you need to use LIKE)

ORDER BY CASE 
           WHEN status LIKE 'active' THEN 1
           WHEN status LIKE 'approved' THEN 2
           WHEN status LIKE 'rejected' THEN 3
           WHEN status LIKE 'submitted' THEN 4
           ELSE 5
         END

MYSQL - create single record out of similar rows, chose values of greatest length for most columns

SELECT max(name),
       place_code,
       max(email),
       max(phone),
       max(address),
       max(details),
       max(estd),
       max(others)
FROM table_x
GROUP BY substring(name,1,4),place_code

Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

You'd need to make a User-Defined Function if you wanted to have syntax similar to your example, but could you do what you want to do, inline, fairly easily with a CASE statement, as the others have said.

The UDF could be something like this:

create function dbo.InlineMax(@val1 int, @val2 int)
returns int
as
begin
  if @val1 > @val2
    return @val1
  return isnull(@val2,@val1)
end

... and you would call it like so ...

SELECT o.OrderId, dbo.InlineMax(o.NegotiatedPrice, o.SuggestedPrice) 
FROM Order o

sql - Return records when a field count is greater than day before

       select x.dateplay as [date], x.players,x.game as Sports, (x.score -isnull(y.score,0)) as more_games
       from
       (SELECT     dateplay, players, game, score,  convert(varchar,dateplay,101) as exp1
        FROM         dbo.players
        where convert(varchar,dateplay,101) = convert(varchar,getdate(),101)) x left outer join 
       (
       SELECT     dateplay, players, game, score, convert(varchar,dateplay,101) as exp1
       FROM         dbo.players
       where convert(varchar,dateplay,101) = convert(varchar,dateadd(day,-1,getdate()),101)
       ) y on x.players =y.players and x.game = y.game -- and  x.score > y.score
       where   (x.score- isnull(y.score, 0))>=0 

try this code, it works fine in SQL

SQL MAX of multiple columns?

This is an old answer and broken in many way.

See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.

Original but problematic answer:

Well, you can use the CASE statement:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

What is the simplest SQL Query to find the second largest value?

SELECT MAX( col )
  FROM table
 WHERE col < ( SELECT MAX( col )
                 FROM table )

---

Related Topics