Beta

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

The problem with SQL

Tet (2721) writes | more than 4 years ago

Databases 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: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.

cancel ×

7 comments

Sorry! There are no comments related to the filter you selected.

Options (1)

Qzukk (229616) | more than 4 years ago | (#30179690)

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 plpgsql;

2) Just use a CASE inline there:

SELECT CASE WHEN c.cutoff>g.cutoff THEN c.cutoff ELSE g.cutoff END as cutoff ...

Needs to be a bit more complex to handle NULL

You have to use a nested select (1)

tomhudson (43916) | more than 4 years ago | (#30180554)

select blah blah blah where select blah blah blah;

Coalesce? (1)

smitty_one_each (243267) | more than 4 years ago | (#30181358)

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

Greatest() (2, Informative)

RedWizzard (192002) | more than 4 years ago | (#30182230)

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

Re:Greatest() (1)

Tet (2721) | more than 4 years ago | (#30182864)

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

Re:Greatest() (1)

Abm0raz (668337) | more than 4 years ago | (#30214426)

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 (1)

Chacham (981) | more than 4 years ago | (#30228924)

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

Check for New Comments
Slashdot Login

Need an Account?

Forgot your password?
or Connect with...

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>