Re: query help

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;