From news1@faith.digsys.bg Wed Jan  12 16:31:59 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 446AD3A5D01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 16:31:59 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 38904-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 16:31:45 +0000 (GMT)
Received: from school.digsys.bg (school.digsys.bg [193.68.6.1])
	by svr1.postgresql.org (Postfix) with ESMTP id 9EB833A5D1B
	for <pgsql-sql@postgresql.org>; Wed, 12 Jan 2005 16:31:42 +0000 (GMT)
Received: from faith.digsys.bg (sigma.digsys.bg [193.68.6.19])
	by school.digsys.bg (8.11.6/8.10.1) with ESMTP id j0CGVdD12883
	for <pgsql-sql@postgresql.org>; Wed, 12 Jan 2005 18:31:39 +0200 (EET)
Message-ID: <41E5529B.9030609@faith.digsys.bg>
Date: Wed, 12 Jan 2005 18:38:51 +0200
From: Kaloyan Iliev Iliev <news1@faith.digsys.bg>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040115
X-Accept-Language: en-us, en, bg
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Problems with HAVING
Content-Type: multipart/alternative;
	boundary="------------030103060809030403080602"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, HTML_40_50, HTML_MESSAGE, HTML_TITLE_EMPTY
X-Spam-Level: 
X-Archive-Number: 200501/54
X-Sequence-Number: 19842

This is a multi-part message in MIME format.
--------------030103060809030403080602
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

My problem is that I want to select the row with max(date) but also 
limited with where clauses.

Select
test.name
from
 test
where
test.name = foo.name
having max(test.date)

This is a subquery and is part bigger query. How I can select the row 
with the max query.

ERROR:  argument of HAVING must be type boolean, not type date


This is the error I receive.
As far as I know I can't use agregate functions in where clause.

Thank you in advance.

Kaloyan Iliev



--------------030103060809030403080602
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hello,<br>
<br>
My problem is that I want to select the row with max(date) but also
limited with where clauses.<br>
<br>
Select <br>
test.name<br>
from <br>
&nbsp;test<br>
where<br>
test.name = foo.name<br>
having max(test.date)<br>
<br>
This is a subquery and is part bigger query. How I can select the row
with the max query.<br>
<pre><font class="result">ERROR:  argument of HAVING must be type boolean, not type date

</font></pre>
<font class="result">This is the error I receive. <br>
As far as I know I can't use agregate functions in where clause.<br>
<br>
Thank you in advance.<br>
<br>
Kaloyan Iliev<br>
</font><br>
<br>
</body>
</html>

--------------030103060809030403080602--



From mike@fuhr.org Wed Jan  12 17:11:39 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5698F3A5D17
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 17:11:39 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 46582-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 17:11:34 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id B16703A5D45
	for <pgsql-sql@postgresql.org>; Wed, 12 Jan 2005 17:11:33 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0CHBLBk051707
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Wed, 12 Jan 2005 10:11:24 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0CHBL99095179;
	Wed, 12 Jan 2005 10:11:21 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0CHBLxP095178;
	Wed, 12 Jan 2005 10:11:21 -0700 (MST) (envelope-from mfuhr)
Date: Wed, 12 Jan 2005 10:11:21 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Kaloyan Iliev Iliev <news1@faith.digsys.bg>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problems with HAVING
Message-ID: <20050112171121.GA94838@winnie.fuhr.org>
References: <41E5529B.9030609@faith.digsys.bg>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <41E5529B.9030609@faith.digsys.bg>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/57
X-Sequence-Number: 19845

On Wed, Jan 12, 2005 at 06:38:51PM +0200, Kaloyan Iliev Iliev wrote:

> My problem is that I want to select the row with max(date) but also 
> limited with where clauses.

If you don't mind using a non-standard feature then try SELECT
DISTINCT ON (not just DISTINCT, but DISTINCT ON):

http://www.postgresql.org/docs/7.4/static/sql-select.html#SQL-DISTINCT

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From sam@samason.me.uk Wed Jan  12 17:40:29 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id CD9093A5D75
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 17:40:29 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 51140-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 17:40:21 +0000 (GMT)
Received: from outmail.freedom2surf.net (outmail1.freedom2surf.net
	[194.106.33.237])
	by svr1.postgresql.org (Postfix) with ESMTP id ECA453A5D70
	for <pgsql-sql@postgresql.org>; Wed, 12 Jan 2005 17:40:20 +0000 (GMT)
Received: from sam.samason.me.uk (sam.samason.me.uk [195.137.31.118])
	by outmail.freedom2surf.net (8.12.10/8.12.10) with ESMTP id
	j0CHeJnF031565
	for <pgsql-sql@postgresql.org>; Wed, 12 Jan 2005 17:40:19 GMT
Received: by sam.samason.me.uk (Postfix, from userid 1000)
	id 1EE1A63716; Wed, 12 Jan 2005 17:40:19 +0000 (GMT)
Date: Wed, 12 Jan 2005 17:40:19 +0000
From: Sam Mason <sam@samason.me.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Problems with HAVING
Message-ID: <20050112174019.GS14073@sam.samason.me.uk>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <41E5529B.9030609@faith.digsys.bg>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-15
Content-Disposition: inline
In-Reply-To: <41E5529B.9030609@faith.digsys.bg>
User-Agent: Mutt/1.5.6i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200501/62
X-Sequence-Number: 19850

Kaloyan Iliev Iliev wrote:
>select test.name
>from test
>where test.name = foo.name
>having max(test.date)

I don't think you use the "having" clause like you've done there.  I
think you want to be doing something more like:

  select test.name
  from test
  where test.name = foo.name
        and test.date in (select max(date) from test);

But I may have misinterpreted you. . .

Cheers,
  Sam



From mike@fuhr.org Wed Jan  12 17:41:51 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 1BFF03A5D38
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 17:41:51 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 50447-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 17:41:40 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 3B78B3A5D17
	for <pgsql-sql@postgresql.org>; Wed, 12 Jan 2005 17:41:37 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0CHfVmr051733
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Wed, 12 Jan 2005 10:41:33 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0CHfUfa095339;
	Wed, 12 Jan 2005 10:41:30 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0CHfUb1095338;
	Wed, 12 Jan 2005 10:41:30 -0700 (MST) (envelope-from mfuhr)
Date: Wed, 12 Jan 2005 10:41:30 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Kaloyan Iliev Iliev <news1@faith.digsys.bg>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problems with HAVING
Message-ID: <20050112174130.GA95306@winnie.fuhr.org>
References: <41E5529B.9030609@faith.digsys.bg>
	<20050112171121.GA94838@winnie.fuhr.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050112171121.GA94838@winnie.fuhr.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/63
X-Sequence-Number: 19851

On Wed, Jan 12, 2005 at 10:11:21AM -0700, Michael Fuhr wrote:
> On Wed, Jan 12, 2005 at 06:38:51PM +0200, Kaloyan Iliev Iliev wrote:
> 
> > My problem is that I want to select the row with max(date) but also 
> > limited with where clauses.
> 
> If you don't mind using a non-standard feature then try SELECT
> DISTINCT ON (not just DISTINCT, but DISTINCT ON):

Sorry, I probably didn't read your message closely enough.  If you
just want a single record then ORDER BY ... LIMIT 1 might be more
appropriate.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From news1@faith.digsys.bg Wed Jan  12 18:32:34 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 639893A5D7F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 18:32:34 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 57205-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 18:32:18 +0000 (GMT)
Received: from school.digsys.bg (school.digsys.bg [193.68.6.1])
	by svr1.postgresql.org (Postfix) with ESMTP id A541A3A5D63
	for <pgsql-sql@postgresql.org>; Wed, 12 Jan 2005 18:32:16 +0000 (GMT)
Received: from faith.digsys.bg (sigma.digsys.bg [193.68.6.19])
	by school.digsys.bg (8.11.6/8.10.1) with ESMTP id j0CIW4D13548;
	Wed, 12 Jan 2005 20:32:05 +0200 (EET)
Message-ID: <41E56ED5.8040005@faith.digsys.bg>
Date: Wed, 12 Jan 2005 20:39:17 +0200
From: Kaloyan Iliev Iliev <news1@faith.digsys.bg>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040115
X-Accept-Language: en-us, en, bg
MIME-Version: 1.0
To: Sam Mason <sam@samason.me.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problems with HAVING
References: <41E5529B.9030609@faith.digsys.bg>
	<20050112174019.GS14073@sam.samason.me.uk>
In-Reply-To: <20050112174019.GS14073@sam.samason.me.uk>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.075 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/68
X-Sequence-Number: 19856

Thanks for the replay first.

Yes I use "having" like I have written. I can't use your query because 
in subquery I must write again the whole WHERE clause.
But in the other mail in the tread there is the solution:)

Thanks again

Kaloyan

Sam Mason wrote:

>Kaloyan Iliev Iliev wrote:
>  
>
>>select test.name
>>    
>>
>>from test
>  
>
>>where test.name = foo.name
>>having max(test.date)
>>    
>>
>
>I don't think you use the "having" clause like you've done there.  I
>think you want to be doing something more like:
>
>  select test.name
>  from test
>  where test.name = foo.name
>        and test.date in (select max(date) from test);
>
>But I may have misinterpreted you. . .
>
>Cheers,
>  Sam
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>  
>



From news1@faith.digsys.bg Wed Jan  12 18:33:52 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 0ACFC3A5D9D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 18:33:52 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 57363-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Jan 2005 18:33:49 +0000 (GMT)
Received: from school.digsys.bg (school.digsys.bg [193.68.6.1])
	by svr1.postgresql.org (Postfix) with ESMTP id ACFD63A5D95
	for <pgsql-sql@postgresql.org>; Wed, 12 Jan 2005 18:33:47 +0000 (GMT)
Received: from faith.digsys.bg (sigma.digsys.bg [193.68.6.19])
	by school.digsys.bg (8.11.6/8.10.1) with ESMTP id j0CIXcD13552;
	Wed, 12 Jan 2005 20:33:38 +0200 (EET)
Message-ID: <41E56F33.8020208@faith.digsys.bg>
Date: Wed, 12 Jan 2005 20:40:51 +0200
From: Kaloyan Iliev Iliev <news1@faith.digsys.bg>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040115
X-Accept-Language: en-us, en, bg
MIME-Version: 1.0
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problems with HAVING
References: <41E5529B.9030609@faith.digsys.bg>
	<20050112171121.GA94838@winnie.fuhr.org>
	<20050112174130.GA95306@winnie.fuhr.org>
In-Reply-To: <20050112174130.GA95306@winnie.fuhr.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.073 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200501/69
X-Sequence-Number: 19857

Thank You,

That's what I need. I know it was something simple but...
Now it works perfectly.

Thank's again.

Kaloyan Iliev

Michael Fuhr wrote:

>On Wed, Jan 12, 2005 at 10:11:21AM -0700, Michael Fuhr wrote:
>  
>
>>On Wed, Jan 12, 2005 at 06:38:51PM +0200, Kaloyan Iliev Iliev wrote:
>>
>>    
>>
>>>My problem is that I want to select the row with max(date) but also 
>>>limited with where clauses.
>>>      
>>>
>>If you don't mind using a non-standard feature then try SELECT
>>DISTINCT ON (not just DISTINCT, but DISTINCT ON):
>>    
>>
>
>Sorry, I probably didn't read your message closely enough.  If you
>just want a single record then ORDER BY ... LIMIT 1 might be more
>appropriate.
>
>  
>