From: Alex Rice <alrice@ARCplanning.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: 2 simple SQL questions: optimizing aggegate query
Date: Mon, 5 May 2003 14:03:18 -0600
Message-ID: <9D0F6008-7F34-11D7-977E-000393529642@ARCplanning.com> (raw)
In-Reply-To: <20030505194518.GA5200@wolff.to>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alrice@ARCplanning.com, bruno@wolff.to
Subject: Re: 2 simple SQL questions: optimizing aggegate query
In-Reply-To: <9D0F6008-7F34-11D7-977E-000393529642@ARCplanning.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox