Thread: 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)
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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
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..
Brendon Gleeson <brendon@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