Обсуждение: query help
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) The result of the query should only return row 4. Is their a way to do this in sql without resorting to proprietary functions, or should I resort to using Ruby (rails app) I have been using the following solution, however it is probably not the best way to do things: "SELECT property_id, address FROM properties LEFT JOIN marketing_campaigns ON marketing_campaigns.property_id = properties.id WHERE ended_on IS NOT NULL AND address LIKE #{SEARCHSTRING} AND property_id NOT IN ( SELECT property_id FROM marketing_campaigns WHERE ended_on IS NULL ORDER BY property_id) GROUP BY property_id, address"
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;
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