Slashdot is powered by your submissions, so send in your scoop


Forgot your password?
DEAL: For $25 - Add A Second Phone Number To Your Smartphone for life! Use promo code SLASHDOT25. Also, Slashdot's Facebook page has a chat bot now. Message it for stories and more. Check out the new SourceForge HTML5 internet speed test! ×

Journal Tet's Journal: The problem with SQL 7

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 = and = 45;

However, what I want to do is:

select mymax(c.cutoff, g.cutoff) from customers c, groups g where c.group_id = and = 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:
  • 1) Enable plpgsql in your database and write your own

    CREATE OR REPLACE FUNCTION mymax (integer, integer) RETURNS integer AS $mymax$
    left ALIAS FOR $1;
    right ALIAS FOR $2;
    IF left IS NULL OR right IS NULL THEN
    END IF;
    IF left>right THEN
    RETURN left;
    RETURN right;
    END IF;
    $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

The aim of science is to seek the simplest explanations of complex facts. Seek simplicity and distrust it. -- Whitehead.