pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHAVING confusion..
3+ messages / 3 participants
[nested] [flat]
* HAVING confusion..
@ 1999-02-21 22:33 George Henry C. Daswani <gdaswani@Vorlon.odc.net>
1999-02-22 07:07 ` Re: [SQL] HAVING confusion.. Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: George Henry C. Daswani @ 1999-02-21 22:33 UTC (permalink / raw)
To: pgsql-sql
Hello,
I'm using postgresql 6.4.2 and i'm having a problem with the
"HAVING" clause..
Basically I do
SELECT u_id,expire_date FROM users HAVING expire_date >= (NOW())
expire_date is of timestamp data type.
and it keeps failing with
ERROR: This is not a valid having query!
even doing something simple like
SELECT u_id from users having u_id > 5
fails with the same error..
select sell_by_date,item_num FROM sell,users,i
tem WHERE sold_by = u_id AND sell_item = item_num AND category_id =
'$parameter'
ORDER BY register_date DESC HAVING sell_by_date >= (NOW())
basically I want to do the above that works with great with mysql
, solid and etc..
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] HAVING confusion..
1999-02-21 22:33 HAVING confusion.. George Henry C. Daswani <gdaswani@Vorlon.odc.net>
@ 1999-02-22 07:07 ` Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Oliver Elphick @ 1999-02-22 07:07 UTC (permalink / raw)
To: gdaswani@odc.net; +Cc: pgsql-sql
"George Henry C. Daswani" wrote:
>Hello,
>
> I'm using postgresql 6.4.2 and i'm having a problem with the
>"HAVING" clause..
>
>
>Basically I do
>
>SELECT u_id,expire_date FROM users HAVING expire_date >= (NOW())
...
>and it keeps failing with
>ERROR: This is not a valid having query!
You should only use HAVING with GROUP BY; for what you are doing, you
just want WHERE.
SELECT u_id,expire_date FROM users
WHERE expire_date >= date(now());
u_id |expire_date
-----+-----------
fred | 21-03-1999
tom | 01-05-1999
harry| 01-05-1999
sarah| 21-03-1999
dick | 12-04-1999
(5 rows)
SELECT expire_date,COUNT(*) FROM users
GROUP BY expire_date
HAVING expire_date >= date(now());
expire_date|count
-----------+-----
21-03-1999| 2
12-04-1999| 1
01-05-1999| 2
(3 rows)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"The LORD bless thee, and keep thee; The LORD make his
face shine upon thee, and be gracious unto thee; The
LORD lift up his countenance upon thee, and give thee
peace." Numbers 6:24-26
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: HAVING confusion..
@ 1999-02-22 15:35 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 1999-02-22 15:35 UTC (permalink / raw)
To: pgsql-sql@hub.org
"George Henry C. Daswani" <gdaswani@Vorlon.odc.net> writes:
> Basically I do
> SELECT u_id,expire_date FROM users HAVING expire_date >= (NOW())
> expire_date is of timestamp data type.
> and it keeps failing with
> ERROR: This is not a valid having query!
It's right: that isn't. You should be using WHERE not HAVING.
WHERE is for selecting individual rows to include or exclude. HAVING is
for selecting groups to list or not list when you have done a grouped
query (GROUP BY). That is, WHERE filters out rows before they are
collected into groups, and HAVING applies to the resulting groups.
So, HAVING without GROUP BY is incorrect.
I agree the error message could be a little more specific...
> basically I want to do the above that works with great with mysql
Evidently mysql is lax about the difference between WHERE and HAVING.
I wonder whether they get it right when you do have a GROUP BY clause?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-02-22 15:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-21 22:33 HAVING confusion.. George Henry C. Daswani <gdaswani@Vorlon.odc.net>
1999-02-22 07:07 ` Oliver Elphick <olly@lfix.co.uk>
1999-02-22 15:35 Re: HAVING confusion.. Tom Lane <tgl@sss.pgh.pa.us>
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