Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.2/8.9.1) with ESMTP id KAA50103 for ; Mon, 22 Feb 1999 10:37:42 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by trends.net (8.8.8/8.8.8) with ESMTP id KAA13636 for ; Mon, 22 Feb 1999 10:37:39 -0500 (EST) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA08696 for ; Mon, 22 Feb 1999 10:35:44 -0500 (EST) To: pgsql-sql@hub.org Subject: Re: HAVING confusion.. In-reply-to: Your message of Sun, 21 Feb 1999 23:00:33 -0500 (EST) <199902220400.XAA82447@hub.org> Date: Mon, 22 Feb 1999 10:35:44 -0500 Message-ID: <8694.919697744@sss.pgh.pa.us> From: Tom Lane "George Henry C. Daswani" 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