Function in SQL Server 2008 similar to GREATEST in mysql?
I want to find the maximum value of multiple columns.
MySQL supports the GREATEST
function but SQL Server doesn't.
Is there any function similar to this in SQL Server 2008?
Not in SQL Server 2008 but these functions are/will be available finally in SQL Server (presumably SQL Server 2022)
the GREATEST and LEAST T-SQL functions are now generally available in Azure SQL Database, as well as in Azure Synapse Analytics (serverless SQL pools only) and Azure SQL Managed Instance.
The functions will also be available in upcoming releases of SQL Server.
For previous versions you can use the fact a sub query can access the columns from the outer query so you can add a sub query Selecting the max
from a union of those.
SELECT *,
(SELECT MAX(c) FROM (VALUES(number),(status)) T (c)) AS Greatest
FROM master..spt_values
Or for SQL Server 2000/2005
SELECT *,
(SELECT MAX(c) FROM
(SELECT number AS c
UNION ALL
SELECT status) T) AS GreatestNumberOrStatus
FROM master..spt_values
For this, I created a scalar function as follows:
CREATE FUNCTION [dbo].[MaxOrNull](@val1 int, @val2 int)
returns int
as
begin
if @val1 >= @val2 RETURN @val1
if @val1 < @val2 RETURN @val2
RETURN NULL
end
It's the most elegant solution and can be used anywhere in your SQL code.