15

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

RiaD
  • 46,822
  • 11
  • 79
  • 123
Colin
  • 2,087
  • 14
  • 16
  • In Fortran, MAX(a, b, ...) did what you want. In SQL, some dialects support functions such as `LARGER` or `SMALLER` or `LARGEST` or `SMALLEST`. There isn't a standard function for the task AFAIK. – Jonathan Leffler Sep 30 '11 at 16:42
  • Beware the dreaded NULL. Do you need: `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), or `CASE WHEN val1 IS NULL THEN val2 WHEN val2 IS NULL THEN val1 WHEN val1 > val2 THEN val1 ELSE val2 END` for `MAX(val1, val2)`. – Jonathan Leffler Sep 30 '11 at 16:45
  • possible duplicate of [Is there a Max function in SQL Server that takes two values like Math.Max in .NET?](http://stackoverflow.com/questions/124417/is-there-a-max-function-in-sql-server-that-takes-two-values-like-math-max-in-net) – Dan J Sep 30 '11 at 16:53
  • It does look like a duplicate. Sorry, I didn't find that thread when I was searching. Mods, feel free to delete. – Colin Sep 30 '11 at 17:01

4 Answers4

19

Functions GREATEST and LEAST are not SQL standard but are in many RDBMSs (e.g., Postgresql). So

SELECT GREATEST(val, 0) FROM mytable;
Andrew Lazarus
  • 18,205
  • 3
  • 35
  • 53
  • Thanks, upvoted. I'm running Microsoft SQL Server. So it looks like those do not exist :(. – Colin Sep 30 '11 at 16:50
2

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).

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
0

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

gbn
  • 422,506
  • 82
  • 585
  • 676
-3

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?

Community
  • 1
  • 1
Anas Karkoukli
  • 1,342
  • 8
  • 13