Re: [GENERAL] HAVING QUESTION
От | Bob Kline |
---|---|
Тема | Re: [GENERAL] HAVING QUESTION |
Дата | |
Msg-id | Pine.LNX.4.10.9911031057470.8497-100000@rksystems.com обсуждение исходный текст |
Ответ на | Re: [GENERAL] HAVING QUESTION (Alexander Barkov <bar@izhcom.ru>) |
Список | pgsql-general |
On Wed, 3 Nov 1999, Alexander Barkov wrote: > 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? (Though 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? > It appears that although some implementations support the syntax you're trying to use, SQL92 (and apparently PostgreSQL) doesn't. What SQL92 *does* support, would be: SELECT url_id, num, rate FROM (SELECT ... FROM ... WHERE ...GROUP BY ...) AS tmptab WHERE num = 3 ORDER BY rate DESC If the DBMS doesn't support this either, then you could resort to creating a temporary table. -- Bob Kline mailto:bkline@rksystems.com http://www.rksystems.com
В списке pgsql-general по дате отправления: