Обсуждение: Problems with HAVING

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 />  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><fontclass="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
inwhere clause.<br /><br /> Thank you in advance.<br /><br /> Kaloyan Iliev<br /></font><br /><br /> 

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/

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

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/

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)
>
>
>  
>

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.
>
>  
>