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;