Tet's Journal: The problem with SQL

I have two tables. One represents individual customers. The other represents groups of customers. For the application I'm writing, I want to be able to apply restrictions to certain things, and I want the flexibility to be able to do that at either the customer level or the group level. So each table has a cutoff column, which says that any order with an ID lower than the cutoff should be ignored for a particular operation. So I can quite happily do:

select c.cutoff, g.cutoff from customers c, groups g where c.group_id = g.id and c.id = 45;

However, what I want to do is:

select mymax(c.cutoff, g.cutoff) from customers c, groups g where c.group_id = g.id and c.id = 45;

The problem is, mymax() doesn't seem to exist. I can't use the regular SQL max(), because that's an aggregate function that works across multiple rows. I want to return the larger of two columns in a single row. Oh, and it should know that any given integer is greater than null, because the common case is for there to be no cutoff, and only very rarely will both a customer and a group have a cutoff. I came up with a hideously complicated way of doing it using coalesce(), but it was so nasty I resorted to doing the comparison in python instead. Am I missing something? Surely this should be easy in SQL. I'm using PostgreSQL 8.3, FWIW.

Options

[Qzukk]

1) Enable plpgsql in your database and write your own

CREATE OR REPLACE FUNCTION mymax (integer, integer) RETURNS integer AS $mymax$
DECLARE
left ALIAS FOR $1;
right ALIAS FOR $2;
BEGIN
IF left IS NULL OR right IS NULL THEN
RETURN NULL;
END IF;
IF left>right THEN
RETURN left;
ELSE
RETURN right;
END IF;
END;
$mymax$ LANGUAGE pl

You have to use a nested select

[tomhudson]

select blah blah blah where select blah blah blah;

Coalesce?

[smittyoneeach]

It sounds like you might want to explore something like that.

Greatest()

[RedWizzard]

The function you are looking for is called greatest().

Re:

[Tet]

Yep. That's exactly what I wanted. Now why couldn't I find it in the documentation when I was looking? Many thanks.

Re:

[Abm0raz]

or use a dynamic temp table sub-select and the HAVING and GROUP BY aggregators.

But honestly, the GREATEST() function will work a lot easier. It's a very CPU intensive function. Writing it out using the dynamic temp table/HAVING/GROUP BY will execute quicker, but will be a lot harder to maintain.

Moo

[Chacham]

As pointed out, it's GREATEST(). In SQL Server, it'd be a CASE statement: CASE WHEN A B THEN B ELSE A END