MySQL to t-SQL

cartch2008
cartch2008 used Ask the Experts™
on
I want to write the following query (written for mysql) for sql server:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

having some trouble...can someone help please?  thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Try:
SELECT TOP 20 id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance;

Open in new window

Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Commented:
T-SQL doesn't support LIMIT.  You can use TOP or if you specify the version of T-SQL you are using, there may be another way.

using TOP

SELECT TOP 20 id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance

Author

Commented:
the problem is not being able to use your custom named column 'distance' in the where or having clauses....any way around that?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
if you run the query, it will say Invalid column name 'distance'.
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Unless someone provides something better, you could use a nested query:
SELECT * FROM
(SELECT TOP 20 id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25) T1
ORDER BY T1.distance;

Open in new window

Author

Commented:
kaufmed...I see what you are getting it, but it still says Invalid column name 'distance'.
check this
select top 20 * from (
select id, 
       ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
FROM markers 
) a
where distance < 25 ORDER BY distance

Open in new window

kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
I'm not sure you can have a "having" without a group by or some aggregate function:
SELECT * FROM
(SELECT TOP 20 ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers) T1
 group by distance
 HAVING distance < 25
ORDER BY T1.distance;

Open in new window

kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Actually, for your purposes, I believe you should change the Having to a Where:
SELECT * FROM
(SELECT TOP 20 id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers) T1
 where distance < 25
ORDER BY T1.distance;

Open in new window

kaufmed, that will not bring the first 20 records where the distance is < 25.  Please check my comment http:#a33147876
awking00Information Technology Specialist

Commented:
See attached.
query.txt
awking00Information Technology Specialist

Commented:
Sorry ralmada,
I didn't see your post and I forgot the stupid alias anyway :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial