Is there something like
select max(val,0)
from table
I'm NOT looking to find the maximum value of the entire table
There has to be an easier way than this right?
select case when val > 0 then val else 0 end
from table
EDIT: I'm using Microsoft SQL Server
Functions GREATEST and LEAST are not SQL standard but are in many RDBMSs (e.g., Postgresql). So
SELECT GREATEST(val, 0) FROM mytable;
Not in SQL per se. But many database engines define a set of functions you can use in SQL statements. Unfortunately, they generally use different names and arguments list.
In MySQL, the function is GREATEST. In SQLite, it's MAX (it works differently with one parameter or more).
Make it a set based JOIN?
SELECT
max(val)
FROM
(
select val
from table
UNION ALL
select 0
) foo
This avoids a scalar udf suggested in the other question which may suit better
What you have in SQL is not even valid SQL. That's not how MAX works in SQL. In T-SQL the MAX aggregates over a range returning the maximum value. What you want is a simply the greater value of two.
Read this for more info:
Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
LARGERorSMALLERorLARGESTorSMALLEST. There isn't a standard function for the task AFAIK.CASE WHEN val IS NULL THEN 0 WHEN val > 0 THEN val ELSE 0 END(for the case where 0 is known not to be null), orCASE WHEN val1 IS NULL THEN val2 WHEN val2 IS NULL THEN val1 WHEN val1 > val2 THEN val1 ELSE val2 ENDforMAX(val1, val2).