Re: 2 simple SQL questions: optimizing aggegate query

On Monday, May 5, 2003, at 01:45  PM, Bruno Wolff III wrote:

>> SELECT url.rec_id, url, title, content_type, txt,
>> sum(
>>   case
>>    when dict.word = 'wordx' then 1
>>    when dict.word = 'wordx' then 1
>>    when dict.word = 'wordy' then 1
>>    else 0
>>   end
>> ) as rank
>> FROM dict, url
>> WHERE url.rec_id = dict.url_id
>> GROUP BY rec_id, url, title, content_type, txt
>> ORDER BY rank DESC
>
> Is there some reason you can't put the word list in the where clause?
> (You can use coalesce to set NULL sums to 0.)

Can you give an example of what you mean use coalesce and how to use it 
to produce a count of the matched words, the rank?

I am converting some existing query examples that use the form
SUM( dict.word IN ( word list...))
which doesn't work with pgsql.

I got the idea for the case statement from this archive message
http://archives.postgresql.org/pgsql-sql/1999-10/msg00136.php

Thanks much,

Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alrice@ARCplanning.com
alrice@swcp.com