pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with HAVING
6+ messages / 3 participants
[nested] [flat]
* Problems with HAVING
@ 2005-01-12 16:38 Kaloyan Iliev Iliev <news1@faith.digsys.bg>
0 siblings, 2 replies; 6+ messages in thread
From: Kaloyan Iliev Iliev @ 2005-01-12 16:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with HAVING
@ 2005-01-12 17:11 Michael Fuhr <mike@fuhr.org>
parent: Kaloyan Iliev Iliev <news1@faith.digsys.bg>
1 sibling, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2005-01-12 17:11 UTC (permalink / raw)
To: Kaloyan Iliev Iliev <news1@faith.digsys.bg>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with HAVING
@ 2005-01-12 17:40 Sam Mason <sam@samason.me.uk>
parent: Kaloyan Iliev Iliev <news1@faith.digsys.bg>
1 sibling, 1 reply; 6+ messages in thread
From: Sam Mason @ 2005-01-12 17:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with HAVING
@ 2005-01-12 17:41 Michael Fuhr <mike@fuhr.org>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Fuhr @ 2005-01-12 17:41 UTC (permalink / raw)
To: Kaloyan Iliev Iliev <news1@faith.digsys.bg>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with HAVING
@ 2005-01-12 18:39 Kaloyan Iliev Iliev <news1@faith.digsys.bg>
parent: Sam Mason <sam@samason.me.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Kaloyan Iliev Iliev @ 2005-01-12 18:39 UTC (permalink / raw)
To: Sam Mason <sam@samason.me.uk>; +Cc: pgsql-sql
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)
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problems with HAVING
@ 2005-01-12 18:40 Kaloyan Iliev Iliev <news1@faith.digsys.bg>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 6+ messages in thread
From: Kaloyan Iliev Iliev @ 2005-01-12 18:40 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
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.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-01-12 18:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-12 16:38 Problems with HAVING Kaloyan Iliev Iliev <news1@faith.digsys.bg>
2005-01-12 17:11 ` Michael Fuhr <mike@fuhr.org>
2005-01-12 17:41 ` Michael Fuhr <mike@fuhr.org>
2005-01-12 18:40 ` Kaloyan Iliev Iliev <news1@faith.digsys.bg>
2005-01-12 17:40 ` Sam Mason <sam@samason.me.uk>
2005-01-12 18:39 ` Kaloyan Iliev Iliev <news1@faith.digsys.bg>
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