On Thu, Sep 17, 2009 at 2:47 PM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
Jonathan <
jharahush@gmail.com> writes:
> Here is my PHP with SQL:
> $query = sprintf("SELECT 'ID', 'FACILITY', 'ADDRESS', latitude,
> longitude, ( 3959 * acos( cos( radians('%s') ) * cos( radians
> ( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin
> ( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance FROM
> aaafacilities HAVING distance < '%s' ORDER BY dist LIMIT 0 OFFSET 20",
refer to output columns of a query in its HAVING clause; it's disallowed
per spec and not logically sensible either. The simplest way to deal
with it is just to repeat the expression in HAVING. If you really
really don't want to write it twice, you can use a subquery.
regards, tom lane
This practice is also a bad habit for MySQL users. I regularly see queries from users that have conditions that logically belong in the WHERE clause but the user shoves it into the HAVING. This is often done without a specific GROUP BY. The MySQL optimizer does not deal with this well.
When would it make logical sense to have a HAVING clause that deals with a column that is not inside a aggregating function?
--
Rob Wultsch
wultsch@gmail.com