Mysql select only latest row when column value changed

Question

this is a similar question here but only 1 difference. the mysql is as follows.

ID |    lat   | lng          | timestamp    
1  | 23.21465 | 84.37856     | 2016-01-01 00:00:00  
2  | 23.21465 | 84.37856     | 2016-01-01 00:03:00  
3  | 23.21584 | 84.37877     | 2016-01-01 00:06:00  
4  | 23.21584 | 84.37877     | 2016-01-01 00:09:00  
5  | 23.21465 | 84.37856     | 2016-01-01 00:12:00  
6  | 23.21465 | 84.37856     | 2016-01-01 00:15:00  
7  | 23.21465 | 84.37856     | 2016-01-01 00:18:00  
8  | 23.21465 | 84.37856     | 2016-01-01 00:21:00

although there is a change in lat(latitude) and lng(longitude) in rows 3 and 5 here in this case it should select only row with id 5 only.

The algorithm would be as follows:

(a) arrange the table in acsending order of timestamp

(b) check last row i.e. 8th row. compare latitude , longitude values with row 7. if they are same precede to compare with row 6 and so on until there is a change in latitude and longitude values . here in this case it would be row 4. then print row 5

(c) if the 8th row and 7the row latitude,longitude values are different then do nothing.

EDIT: In a nutshell, I'm simply after the earliest instance of the current values, or the last time the values changed.

have you tried the accepted answer. If your DB version is 8, then even no need to use such assignments, rather window analytic functions. — Barbaros Özhan, Dec 15 '19 at 06:12
I don't understand, why only row 5. an algorithm needs deterministic rules, to work. Properly. the link would deliver 3 rows. — nbk, Dec 15 '19 at 08:29
Are you simply after the earliest instance of the current values (I.e., the most recent change) — Strawberry, Dec 15 '19 at 08:55
@nbk this is use to determine that the vehicle is stopping at that location for how much time. the table is the output of the gps tracker — bipin_s, Dec 15 '19 at 10:25
@Strawberry , it is the earliest instance of the current values — bipin_s, Dec 15 '19 at 10:26

LukStorms · Accepted Answer · 2019-12-16T09:43:17.803

If you want the first record when the latitude changed the last time?

Then here's a way to get that.

Sample data

create table YourLatiLongiTudesTable
(
  ID int primary key auto_increment, 
  lat decimal(8,5) not null, 
  lng decimal(8,5) not null,
  `timestamp` timestamp not null
);

insert into YourLatiLongiTudesTable
(lat, lng, `timestamp`) values                              
('23.21465', '84.37856', '2016-01-01 00:00'),
('23.21465', '84.37856', '2016-01-01 00:03'),
('23.21584', '84.37877', '2016-01-01 00:06'),
('23.21584', '84.37877', '2016-01-01 00:09'),
('23.21465', '84.37856', '2016-01-01 00:12'),
('23.21465', '84.37856', '2016-01-01 00:15'),
('23.21465', '84.37856', '2016-01-01 00:18'),
('23.21465', '84.37856', '2016-01-01 00:21')
;

Query for MySql 5.7

--
-- Using variables
-- First calculate a rank on descending date
-- Then calculate a rownum based on the rank
--
SELECT id, lat, lng, `timestamp`
-- , rnk, rnk_rn
FROM
(
  SELECT *
  , CASE 
    WHEN rnk != @prev_rnk
     AND @prev_rnk := rnk
    THEN @rn := 1
    WHEN @prev_rnk := rnk
    THEN @rn := @rn + 1
    END AS rnk_rn
  FROM
  (
    SELECT t.*
    , CASE 
      WHEN lat != @prev_lat
       AND @prev_lat := lat 
      THEN @rnk := @rnk + 1
      WHEN @prev_lat := lat 
      THEN @rnk
      END AS rnk
    FROM YourLatiLongiTudesTable t
    CROSS JOIN (SELECT @rnk:=0, @prev_lat:=0) v
    ORDER BY `timestamp` desc, id desc
  ) q1
  CROSS JOIN (SELECT @rn:=0, @prev_rnk := 0) v
  ORDER BY `timestamp`, id
) q2
WHERE rnk = 1 
  AND rnk_rn = 1
;

id |      lat |      lng | timestamp          
-: | -------: | -------: | :------------------
 5 | 23.21465 | 84.37856 | 2016-01-01 00:12:00

Query for MySql 8.0+ (and other RDBMS that support window functions):

--
-- Using window functions
-- First calculate a rank by summing changes.
-- Then calculate a rownum based on the rank
--
SELECT id, lat, lng, `timestamp`
-- , rnk, rnk_rn
FROM
(
  SELECT *
  , ROW_NUMBER() OVER (PARTITION BY date(`timestamp`), rnk ORDER BY `timestamp`, id) AS rnk_rn
  FROM 
  (
    SELECT *
    , SUM(IF(lat=prev_lat,0,1)) OVER (PARTITION BY date(`timestamp`) ORDER BY `timestamp` DESC, id DESC) AS rnk
      FROM 
      (
        SELECT *
        , LAG(lat) OVER (PARTITION BY date(`timestamp`) ORDER BY `timestamp` DESC, id DESC) AS prev_lat
        FROM YourLatiLongiTudesTable t
      ) q1
   ) q2
) q3
WHERE rnk = 1
  AND rnk_rn = 1
ORDER BY `timestamp`, id;

id |      lat |      lng | timestamp          
-: | -------: | -------: | :------------------
 5 | 23.21465 | 84.37856 | 2016-01-01 00:12:00

db<>fiddle here

@LukStorms rather comparing latitudes, time-stamp compare would be more easier — bipin_s, Dec 15 '19 at 10:22
@LukStorms the algorithm would be as follows: (a) arrange the table in acsending order of timestamp (b) check last row i.e. 8th row. compare latitude , longitude values with row 7. if they are same precede to compare with row 6 and so on until there is a change in latitude and longitude values . here in this case it would be row 4. then print row 5 (c) if the 8th row and 7the row latitude,longitude values are different then do nothing. coding the above in mysql statements is what i need.Thanks — bipin_s, Dec 16 '19 at 00:42

Mysql select only latest row when column value changed

1 Answers1