Re: query help

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