Skip site navigation (1) Skip section navigation (2)

Re: query help

From: "Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: query help
Date: 2006-07-27 19:06:23
Message-ID: 1154027183.719063.44460@b28g2000cwb.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-novice
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;


In response to

  • query help at 2006-07-27 08:00:20 from Brendon Gleeson

Responses

pgsql-novice by date

Next:From: Andrew HammondDate: 2006-07-27 19:59:50
Subject: Re: query help
Previous:From: Andrew HammondDate: 2006-07-27 18:52:03
Subject: Re: PostgreSQL Replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group