Forgot your password?
typodupeerror
Databases

Tet's Journal: The problem with SQL 7

Journal by Tet
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.

This discussion has been archived. No new comments can be posted.

The problem with SQL

Comments Filter:
  • by Qzukk (229616)

    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

  • select blah blah blah where select blah blah blah;

  • It sounds like you might want to explore something like that.
  • Greatest() (Score:3, Informative)

    by RedWizzard (192002) on Saturday November 21, 2009 @01:41AM (#30182230)
    The function you are looking for is called greatest().
    • by Tet (2721) *
      Yep. That's exactly what I wanted. Now why couldn't I find it in the documentation when I was looking? Many thanks.
      • by Abm0raz (668337) *

        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.

  • by Chacham (981)

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

At work, the authority of a person is inversely proportional to the number of pens that person is carrying.

Working...