help using column alias in where 2

[figment88]

I'm trying to select stores from a database within 3 miles of a location. I have the longitude and latitude in the database stored in radians to help ease runtime computation.

I want to get distance in the select, where, and order by clauses. In order not to have to do the trig multiple timesI want to use an alias. The order by works fine, but the I can't get the select to work.

The code with just the order by that works looks like

SELECT name, ( ACOS( SIN( 0.6598463936 ) * SIN( lat_radians ) + COS( 0.6598463936 ) * COS( lat_radians ) * COS( lon_radians - - 2.1343445941 ) ) * 3956 ) AS dist
FROM stores
ORDER BY `dist` ASC

Adding in the where clause blows-up

SELECT name, ( ACOS( SIN( 0.6598463936 ) * SIN( lat_radians ) + COS( 0.6598463936 ) * COS( lat_radians ) * COS( lon_radians - - 2.1343445941 ) ) * 3956 ) AS dist
FROM stores
WHERE `dist` < 3
ORDER BY `dist` ASC

I'm really weak at sql, so any help appreciated.

 

[PHV]

Usually the WHERE clause is evaluated BEFORE the SELECT list, so I'm afraid you can't use column's alias in this clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LKBrwnDBA]

But you can use the HAVING clause:

SELECT name, ( ACOS( SIN( 0.6598463936 ) * SIN( lat_radians ) + COS( 0.6598463936 ) * COS( lat_radians ) * COS( lon_radians - - 2.1343445941 ) ) * 3956 ) AS dist
FROM stores
ORDER BY 2 ASC
HAVING ( ACOS( SIN( 0.6598463936 ) * SIN( lat_radians ) + COS( 0.6598463936 ) * COS( lat_radians ) * COS( lon_radians - - 2.1343445941 ) ) * 3956 ) <3;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[r937]

that may work but i personally don't like using a HAVING clause without a GROUP BY

probably just a personal pecadillo, though

how about this --

select name
     , dist
  from (
       [COLOR=red]select name
            , ( acos( sin( 0.6598463936 ) 
                    * sin( lat_radians ) 
                    + cos( 0.6598463936 ) 
                    * cos( lat_radians ) 
                    * cos( lon_radians 
                  - - 2.1343445941 ) ) 
                       * 3956 ) as dist
         from stores[/color]
       ) as mydata
 where dist < 3
order 
    by dist asc

p.s. what's with the double negative signs?

r937.com | rudy.ca

 

[figment88]

Thanks for the help everyone.

PHV - I was worried about the processing order. Certainly seems to throw a wrench in the works.

LKBrwnDBA - Your suggestion looks like it would work, but I was trying not to compute the trig on all the data twice. I think I could also just repeat in a WHERE clause. Is there an advantage with HAVING such as the SELECT would only work on the subset of data meeting the condition. If so, that would probably be workable.

r937 - Your suggestion looks really interesting. Unfortunately my database blows up at the second select. Maybe mySQL does not allow for this type of construction? As to the double negative, - 2.1343445941 is the longitude of my test zip code in radians. In practice this will be a variable that will be difference for each user.

thanks again all - you've definately given me avenues to explore.

 

[r937]

your database "blows up" on the subquery? gee, i hope nobody in the data centre was hurt

two suggestions:

please post MySQL questions in the MySQL forum (this is ANSI SQL here)

please update to at least version 4.1, the version you are on is a dinosaur

r937.com | rudy.ca

 

[LKBrwnDBA]

>> I was trying not to compute the trig on all the data twice.

FYI:

Even you repeat the computations in th e"having" clause, they are NOT computed twice.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[figment88]

r937 was absolutely correct. I updated my db to the latest version which includes support for derived tables and everything works like a charm.

Sorry for posting in the wrong group - didn't realize there was a seperate mySQL forum. Still, I would have probably ended up posting here eventually - the quality of the posts seem to be higher.

Thanks all for your help.

P.S. Noboy was hurt when the db blew up, but I did manage to take out a server in a neighboring cage.

 

[Golom]

... the quality of the posts seem to be higher

Don't know about that ... but they are probably more specific to mySQL. Some of the people on this forum (e.g. PHV, r937) are so good they're almost scary.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]