Converting 'WITH CTE' MSSQL to MySQL

Question

I realize that this doesn't directly translate from MSSQL to MySQL but I'm not not sure how to make it work. Any help that you have is appreciated.

;WITH cte As (
SELECT
    post_id,
    status, 
    dealer, 
    distributor,
    SUM( 
        3959 * acos( 
        cos( radians(%f) ) * 
        cos( radians( lat ) ) * 
        cos( radians( lng ) - radians(%f) ) + 
        sin( radians(%f) ) * 
        sin( radians( lat ) ) 
        ) 
    ) 
    AS DISTANCE
    FROM wp_geodatastore
    GROUP BY post_id, status, dealer, distributor       
)
SELECT post_id, status, dealer, distributor, DISTANCE
FROM cte WHERE (DISTANCE < %d) 
AND status = 'publish' AND dealer = 'on' AND distributor = 'on'
ORDER BY DISTANCE
OFFSET %d ROWS
FETCH NEXT %d ROWS ONLY;

You can't - see http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — Kenan Zahirovic, Mar 18 '15 at 02:21
possible duplicate of [How to transform a MSSQL CTE query to MySQL?](http://stackoverflow.com/questions/8833535/how-to-transform-a-mssql-cte-query-to-mysql) — Kenan Zahirovic, Mar 18 '15 at 02:22
I did see that post, but I'm not experienced enough to know what to do with the suggestions. TEMPORARY tables, DERIVED tables, inline views? — Michelle, Mar 18 '15 at 02:23
I'd try with temp tables, maybe not so elegant solution but definitely should work. — Kenan Zahirovic, Mar 18 '15 at 02:25
Thanks Kenan for your help, it looks like the answer was to make it a subquery. See below. — Michelle, Mar 18 '15 at 02:59

score 1 · Accepted Answer · answered Mar 18 '15 at 02:29

Just make it a subquery:

SELECT post_id, status, dealer, distributor, DISTANCE
FROM (SELECT post_id, status, dealer, distributor,
             SUM( 3959 * acos( 
                 cos( radians(%f) ) * 
                 cos( radians( lat ) ) * 
                 cos( radians( lng ) - radians(%f) ) + 
                 sin( radians(%f) ) * 
                 sin( radians( lat ) ) 
                ) 
               ) AS DISTANCE
      FROM wp_geodatastore
      GROUP BY post_id, status, dealer, distributor       
     ) cte
WHERE (DISTANCE < %d) AND
      status = 'publish' AND dealer = 'on' AND distributor = 'on'
ORDER BY DISTANCE
OFFSET %d ROWS
FETCH NEXT %d ROWS ONLY;

Converting 'WITH CTE' MSSQL to MySQL

1 Answers1

Linked