Journal Tet's Journal: The problem with SQL 7
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 (Score:2)
1) Enable plpgsql in your database and write your own
You have to use a nested select (Score:2)
select blah blah blah where select blah blah blah;
Coalesce? (Score:1)
Greatest() (Score:3, Informative)
Re: (Score:2)
Re: (Score:2)
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 (Score:1)
As pointed out, it's GREATEST(). In SQL Server, it'd be a CASE statement: CASE WHEN A B THEN B ELSE A END