Menu
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?
870
No, MySQL is not the same as SQL server. Both of these are relational database management systems offered by different vendors. They differ in terms of use cases, licensing, pricing, features, pros, cons, etc. MySQL is offered through Oracle and SQL Server is offered through Microsoft corporation.
The GREATEST() function returns the greatest value of the list of arguments.
In terms of data security, the SQL server is much more secure than the MySQL server. In SQL, external processes (like third-party apps) cannot access or manipulate the data directly. While in MySQL, one can easily manipulate or modify the database files during run time using binaries.
The Oracle GREATEST function returns the “greatest” or largest value in a set of values that you provide to it. The Oracle LEAST function returns the “least” or smallest value in a set of values that you provide to it, and it's the opposite of the GREATEST function.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
