From: | Alexander Barkov <bar(at)izhcom(dot)ru> |
---|---|
To: | |
Cc: | "'pgsql-general(at)hub(dot)org'" <pgsql-general(at)hub(dot)org> |
Subject: | Re: [GENERAL] HAVING QUESTION |
Date: | 1999-11-03 16:01:50 |
Message-ID: | 38205C6E.9BC6F2F6@izhcom.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Bob Kline wrote:
>
> On Wed, 3 Nov 1999, Alexander Barkov wrote:
>
> >
> > Hi!
> >
> >
> > How can I refer the calculated field in HAVING clause.
> >
> > This work in MySQL:
> >
> > SELECT some_expression as field1, ...
> > FROM tablename
> > GROUP BY ...
> > HAVING field1>0;
> >
> > PostgreSQL gives error "Attribute 'field1' not found".
> >
> >
> > Are there any workarounds?
> >
> >
>
> How about HAVING some_expression > 0? (Thought your version is legal
> SQL, I believe).
>
The problem is that some_expression is big enough. I need
this query for search engine. The query depends of number of
given words. Check second field in this query:
SELECT
dict.url_id,
max(case when word IN ('word1') then 1 else 0 end)+
max(case when word IN ('word2') then 1 else 0 end)+
max(case when word IN ('word3') then 1 else 0 end) as num,
sum(intag)as rate
FROM dict,url
WHERE url.rec_id=dict.url_id
AND dict.word IN ('word1','word2','word3')
GROUP BY dict.url_id ORDER BY num DESC, rate DESC
I need to check in HAVING that calculated field 'num' is 3.
This is the sample for three words. I can duplicate big expression
for 'num' in HAVING. But the query will be huge for 10 or 15 words :-)
Strange. I cannot use 'num' in HAVING. But it works in ORDER BY.
May be I'm doing something wrong?
--
Alexander Barkov
IZHCOM, Izhevsk
email: bar(at)izhcom(dot)ru | http://www.izhcom.ru
Phone: +7 (3412) 51-55-45 | Fax: +7 (3412) 78-70-10
ICQ: 7748759
From | Date | Subject | |
---|---|---|---|
Next Message | Bob Kline | 1999-11-03 16:27:01 | Re: [GENERAL] HAVING QUESTION |
Previous Message | Scott Perkins | 1999-11-03 15:36:36 | Club/Assoc/Roster Mgt App |