fetch rows based on another activity in mysql

Question

I have one mysql table of user_locations. I have records like below

id    user_id     latitude                 longitude
 1      1         28.62584611111111        77.10560666666667
 2      1         28.62584611111111        77.10560666666667
 3      1         28.62584611111111        77.10560666666667
 4      1         28.62584611111111        77.10560666666667
 5      1         28.627457222222223       77.11092111111111
 6      1         28.627457222222223       77.11092111111111
 7      1         28.62584611111111        77.10560666666667
 8      1         28.62584611111111        77.10560666666667

Now see id 1,2,3,4,7,8 having same latitude and longitude and id 5,6 having different latitude longitude

if i group by i get only two results. But My expected output is like below:-

 id    user_id     latitude                 longitude
 4      1         28.62584611111111        77.10560666666667
 6      1         28.627457222222223       77.11092111111111
 8      1         28.62584611111111        77.10560666666667

I have tried below query but that gives me two results

SELECT * FROM `user_locations` WHERE `user_id` = 1 Group by latitude, longitude

What i want to find if same latitude and longitude constantly occur it must return one latest row and after that if changes in latitude and longitude then that latest row will come and if in between again same latitude and longitude came from pevious sets that should also return. Hope you understand my problem. Can anyone help me to resolve this issue.

Answer 1

This is a gaps-and-islands problem. Islands represents adjacent rows having the same user, latitude and longitude, and you want the last record of each island.

Here the simplest approach is probably to use lag() to identify the changes in latitude/longitude:

select *
from (
    select ul.*,
        lag(latitude) over(partition by user_id order by id) lag_latitude,
        lag(longitude) over(partition by user_id order by id) lag_longitude
    from user_locations ul
) ul
where latitude <> lag_latitude or longitude <> lag_longitude

---

This requires MariaDB 12.2.2 or higher. In earlier versions, you can emulate window functions with correlated subqueries. This will probably be inefficient over a large number of rows:

select *
from user_locations ul
where latitude  <> (select ul1.latitude  from user_locations ul1 where ul1.user_id = ul.user_id and ul1.id < ul.id order by ul1.id desc limit 1)
   or longitude <> (select ul1.longitude from user_locations ul1 where ul1.user_id = ul.user_id and ul1.id < ul.id order by ul1.id desc limit 1)