How to MYSQL select results into temporary table (without using a procedure)?-CodePudding

I have a table of coordinates and a select statement that I am using to calculate the distances between coordinates and keep their IDs.

This is the working statement that I have:

SELECT haversineDistance(-38.483858, 176.338444, latitude, longitude) AS distances, id FROM COORDINATES;

The first two numbers are the latitude and longitude of the first coordinate (which I am changing in my code but that does not matter for this question). I need to get the ID of the coordinate with the smallest distance from the first coordinate.To do that, I tried to find the minimum distance from the results I got using an SQL query, something like this:

`SELECT haversineDistance(-38.483858,176.338444, latitude, longitude) AS distances, id INTO #Distances FROM COORDINATES;

SELECT MIN(distances), id FROM #Distances;`

But this does not work, the syntax is wrong for MySQL. I am getting Error code 1064. You have an error in your SQL syntax.

Is there a way to do this without using a procedure to create a temporary table (this was asked before but went unanswered)?

CodePudding user response：

To find the minimum distance and the corresponding ID from your COORDINATES table, you can use a subquery in the SELECT statement, like this:

SELECT MIN(distances) AS min_distance,
       (SELECT id FROM COORDINATES
        WHERE haversineDistance(-38.483858, 176.338444, latitude, longitude) = min_distance) AS id
FROM (
  SELECT haversineDistance(-38.483858, 176.338444, latitude, longitude) AS distances
  FROM COORDINATES
) distances;

In this example, we use a subquery in the SELECT statement to find the id of the coordinate with the minimum distance. The subquery is nested inside the MIN() function, which returns the minimum value of the distances column from the outer query.

Note that this approach does not use a temporary table, so you do not need to use a stored procedure.

I hope this helps!

Edit: To use the min_distance alias in the subquery, you need to use the WITH clause to define the alias as a common table expression (CTE), like this:

WITH distances AS (
  SELECT haversineDistance(-38.483858, 176.338444, latitude, longitude) AS distances
  FROM COORDINATES
)
SELECT MIN(distances.distances) AS min_distance,
       (SELECT id FROM COORDINATES
        WHERE haversineDistance(-38.483858, 176.338444, latitude, longitude) = min_distance) AS id
FROM distances;```