query help

[Brendon Gleeson]

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"

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Andrew Hammond]

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;

[Andrew Hammond]

On 7/27/06, Brendon Gleeson <bre...@gleesonprop.co.za> wrote:

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

[Brendon Gleeson]

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)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

[Jim C. Nasby]

On Wed, Jul 26, 2006 at 01:37:05PM +0200, 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)

#define 'old campaigns' and 'current'

> 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)

No, this can certainly be done in SQL, though if you have pseudo or ruby
code on how you'd do it, it might make it a bit more clear on what
you're after.
--
Jim C. Nasby, Sr. Engineering Consultant jna...@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

[Keith Worthington]

Jim C. Nasby wrote:
> On Wed, Jul 26, 2006 at 01:37:05PM +0200, 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)
>
>
> #define 'old campaigns' and 'current'
>
>
>>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)
>
>
> No, this can certainly be done in SQL, though if you have pseudo or ruby
> code on how you'd do it, it might make it a bit more clear on what
> you're after.

I can't test this at the moment but how about something like this.

SELECT *
FROM marketing_campaigns
WHERE date_ended IS NOT NULL

AND property_id NOT IN ( SELECT property_id
FROM marketing_campaigns

WHERE date_ended IS NULL
)
ORDER BY id;

--

Kind Regards,
Keith

[Brendon Gleeson]

Jim C. Nasby wrote:
...

> No, this can certainly be done in SQL, though if you have pseudo or ruby
> code on how you'd do it, it might make it a bit more clear on what
> you're after.

...
I managed to find a suitable solution for this:
(I am using postgres 7.4)

SELECT property_id, address FROM marketing_campaigns
LEFT JOIN properties ON properties.id = marketing_campaigns.property_id
WHERE address LIKE *?
GROUP BY property_id, address
HAVING count(ended_on) = **count(1)

* variable
** apparently count(1) is faster than count(*) and count(id), my Rails log
confirms this, however I have a limited amount of records at the moment so
benchmarks are properly inaccurate..

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Tom Lane]

Brendon Gleeson <bre...@gleesonprop.co.za> writes:
> ** apparently count(1) is faster than count(*) and count(id), my Rails log
> confirms this, however I have a limited amount of records at the moment so
> benchmarks are properly inaccurate..

count(1) and count(*) are exactly the same thing (in existing PG
releases anyway), so whatever you're looking at is measurement noise.
count(id) would properly be slower because of the need to examine the
field to see if it's null.

regards, tom lane