Skip site navigation (1) Skip section navigation (2)

Re: query help

From: "Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: query help
Date: 2006-07-27 19:59:50
Message-ID: 1154030390.905275.104000@p79g2000cwp.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 7/27/06, Brendon Gleeson <brendon(at)gleesonprop(dot)co(dot)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


In response to

pgsql-novice by date

Next:From: Derrick BettsDate: 2006-07-28 05:09:32
Subject: Copy Schema
Previous:From: Andrew HammondDate: 2006-07-27 19:06:23
Subject: Re: query help

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group