Re: query help

On 7/27/06, Brendon Gleeson <brendon@gleesonprop.co.za> wrote:

    Andrew Hammond wrote:
    > Brendon Gleeson wrote:
    >> I have a table called "marketing_campaigns":
    >>
    >> marketing_campaigns:
    >> +----+--------------+------------+-------------+-------------
    >> | id | date_started | date_ended | property_id | status
    >> +----+--------------+------------+-------------+-------------
    >> | 1  |  2005-01-01  | 2005-03-12 |  5          | sold
    >> | 2  |  2006-01-11  | 2006-02-23 |  5          | jointly sold
    >> | 3  |  2006-05-13  | NULL       |  5          | for sale
    >> | 4  |  2006-02-01  | 2006-02-06 |  6          | sold
    >>
    >> I am having trouble trying to say: Only show old campaigns And
only if
    >> their is not a "current" campaign for this property(property_id)
    >
    > Assuming your ids are temporally ordered,
    >
    > SELECT * FROM marketing_campaigns
    > WHERE id IN (SELECT max(id) FROM marketing_campaigns
    >     ORDER BY COALESCE(date_ended, 'infinity'::timestamp)
    >     GROUP BY property_id)
    >   AND date_ended IS NOT NULL;

    Thanks, I got it to work. (GROUP BY before ORDER BY ;-))

    SELECT * FROM marketing_campaigns
    WHERE id IN (
       SELECT max(id) FROM marketing_campaigns
       GROUP BY property_id
       ORDER BY COALESCE(MAX(date_ended), 'infinity'::timestamp)
    ) AND date_ended IS NOT NULL;

    Can I actually rely on postgres to keep incrementing the id's
properly when
    migrating to another server? otherwise this is going to get a bit
troublesome..

Your call to MAX in the coalesce is unnecessary.

That depends how you're implementing the increment in the first place.
If you're using DEFAULT (nextval(my_sequence)); and you do a pg_dump /
restore, then yes, it's reasonable to expect things to migrate safely.

Brandon, please direct your responses to the list, not to my personal
email address.

Drew