Dynamic scope on related model and then sorting by distance

Question

I have a table where I keep logs of locations of all users from where I need to grab the latest location of all the users and sort it by the distance from provided geometry point.

Locations table

id	user_id	location	time
1	1	POINT	timestamp
2	1	POINT	timestamp
3	2	POINT	timestamp
4	2	POINT	timestamp

The result should be

id	user_id	location	time
2	1	POINT	timestamp
4	2	POINT	timestamp

Then I need to sort users who are nearest to the provided point. I got how I can use MySQL Spatial function to get the distance but unable to sort with result above. I refereed this for getting latest location and this from another stack-overflow answer but having hard time using both together.

I appreciate any help and thanks in advance

Answer 1

WITH cte AS ( SELECT source_table.*, 
                     ROW_NUMBER() OVER (PARTITION BY source_table.user_id 
                                        ORDER BY source_table.time DESC) rn
              FROM source_table )
SELECT cte.*
FROM cte
WHERE cte.rn = 1 
ORDER BY ST_Distance(cte.location, @specified_point)