Обсуждение: 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