79800

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;

Answer1:

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;

Recommend

  • Select in select query cakephp
  • List combinations in defined range
  • Title over group of subplots
  • Finding nearest value in a mysqli array php
  • SQL Server 2008R2 Transactional Replication - Move SubscriberDB - Push Subscription
  • Adding a calendar to my website
  • Disabling interrupt with the ARM GIC (global interrupt controller)
  • Eloquent finding the row with the max value with grouping
  • How to hide an alias column in mysql EDITED
  • Click event in iframe
  • Using result of first select statement into another and store result into another table
  • Why echoing a local variable inside the same function is not working? [duplicate]
  • NoReverseMatch error when running tests in Django
  • No callback with FB.ui
  • How to select parent ids
  • How to setup a reverse proxy on several ports (tcp & udp)
  • How to read stringified JSON string in Java [duplicate]
  • Passing Query Parameters from HTML/JS App to Azure Server Script
  • Geometry shader doesn't do anything when fed GL_POINTS
  • Getting Coordinates of a Point in a circle
  • What is the best data type to store boolean values in a database
  • Python sin and cosine giving incorrect values
  • How can I use a custom function within an expression using the eval dataframe method?
  • 2-table interaction: insert, get result, insert
  • d3.js: why is d3.geo.path() giving NaN?
  • Request response issues in biztalk
  • uml Composition relationships to RDF and OWL
  • Can't remove headers after they are sent
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Approximate Order-Preserving Huffman Code
  • D3 get axis values on zoom event
  • How to use JavaScript to determine whether a file exists in a directory?
  • Grails calculated field in SQL
  • How do I pass the string value parameter of the selected list item from an auto-populated dropdown l
  • Is possible to count alias result on mysql
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?