In SQL, are there non-aggregate min / max operators
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 c开发者_JS百科ase 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?
精彩评论