pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
HAVING 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