MySQL difference between two rows of a SELECT Statement

Question

I am trying to make the difference of two rows in an mysql database.
I have this table containing ID, kilometers, date, car_id, car_driver etc...
Since I don't always enter the information in the table in the correct order, I may end up with information like this:

ID | Kilometers | date | car_id | car_driver | ...
 1 | 100        | 2012-05-04 | 1 | 1  
 2 | 200        | 2012-05-08 | 1 | 1
 3 | 1000       | 2012-05-25 | 1 | 1 
 4 | 600        | 2012-05-16 | 1 | 1

With a select statement I am able to sort my table correctly:

SELECT * FROM mytable ORDER BY car_driver ASC, car_id ASC, date ASC

I will obtain this:

ID | Kilometers | date  | car_id | car_driver | ...  
 1 | 100        | 2012-05-04 | 1 | 1  
 2 | 200        | 2012-05-08 | 1 | 1
 4 | 600        | 2012-05-16 | 1 | 1  
 3 | 1000       | 2012-05-25 | 1 | 1

Now I would like to make a view where basically I have this extra information: Number of kilometers since last date and I would like to obtain something like this:

ID | Kilometers | date       | car_id | car_driver | number_km_since_last_date   
 1 | 100        | 2012-05-04 | 1 | 1 | 0  
 2 | 200        | 2012-05-08 | 1 | 1 | 100  
 4 | 600        | 2012-05-16 | 1 | 1 | 400  
 3 | 1000       | 2012-05-25 | 1 | 1 | 400

I thought of doing an INNER JOIN to perform what I wanted, but I have the feeling I can't do the join on my ID since they are not sorted correctly.
Is there a way to achieve what I want?

Shall I create a view with a sort of row_number that I can then used in my INNER JOIN?

Is there a reason that this has be done exclusively with MySQL? — Explosion Pills, Feb 13 '13 at 15:40
Explosion Pills, I do it in MySQL because it's what's provided with my hosting site — user1108276, Feb 13 '13 at 15:44
Imre M, In fact I have other columns in my table that I use for my sorting. I didn't think it was important to be mentioned there. The thing is that I can sort my table like I want with a SELECT statement but then my ID's are in a random order. — user1108276, Feb 13 '13 at 15:46
Would be so easy with a modern DBMS supporting window functions and `lag()` — , Feb 13 '13 at 15:50
@user1108276 I'm assuming that you want the KM since last date on a per driver, per car basis... correct? — Michael Fredrickson, Feb 13 '13 at 16:09
Do you want the kilometers difference to start from 0 again for every `car_id` or for every `car_driver`? Because that's a slightly different question which (could be solved easily with `PARTITION BY` in other DBMS but) would make MySQL solutions even more complex. — ypercubeᵀᴹ, Feb 13 '13 at 16:37
Ideally yes, I would like this to be set again to 0 for every car_id. Regarding the car_driver I think I will query per car_driver so that should not be a problem. So my table is sorted correctly via my Select Statement and then basically I need to go row after row to calculate the km done, when it find a new car, it sets again 0 and continue — user1108276, Feb 14 '13 at 10:15

Michael Fredrickson · Answer 1 · 2013-02-13T15:59:14.513

27

SELECT
    mt1.ID,
    mt1.Kilometers,
    mt1.date,
    mt1.Kilometers - IFNULL(mt2.Kilometers, 0) AS number_km_since_last_date   
FROM
    myTable mt1
    LEFT JOIN myTable mt2
        ON mt2.Date = (
            SELECT MAX(Date)
            FROM myTable mt3
            WHERE mt3.Date < mt1.Date
        )
ORDER BY mt1.date

Sql Fiddle

Or, by emulating a lag() function through MySql hackiness...

SET @kilo=0;

SELECT
    mt1.ID,
    mt1.Kilometers - @kilo AS number_km_since_last_date,
    @kilo := mt1.Kilometers Kilometers,
    mt1.date
FROM myTable mt1
ORDER BY mt1.date

Sql Fiddle

edited Feb 13 '13 at 15:59

answered Feb 13 '13 at 15:48

Michael Fredrickson

36,839
5
92
109

Thanks you actually answered my question, however I should have maybe ask it in a more complete way. Actually my Select statement is not done only on date, but on other criteria like: car_id, car_driver etc... so I have more than I column in my ORDER BY. – user1108276 Feb 13 '13 at 15:55
2

@user1108276 If you update your question with the additional details / criteria, I can provide you a better answer... – Michael Fredrickson Feb 13 '13 at 16:00
Any other idea Mickael? That would be very helpful – user1108276 Feb 14 '13 at 10:11
1

I think we don't need the 3rd table, ``` LEFT JOIN myTable mt2 ON mt2.Date = ( SELECT MAX(Date) FROM myTable mt2 WHERE mt2.Date < mt1.Date ) ``` – yusong Oct 23 '19 at 06:56

ypercubeᵀᴹ · Answer 2 · 2013-02-14T13:36:43.967

In Postgres, Oracle and SQL-Server 2012, this is plain simple, using the LAG() function:

SELECT
    id, kilometers, date,
    kilometers 
    - COALESCE( LAG(kilometers) OVER (ORDER BY date ASC, car_driver ASC, id ASC)
              , kilometers) 
        AS number_km_since_last_date
FROM
    mytable ;

In MySQL, we have to do some nasty constructions. Either an inline subquery (with probably not very good performance):

SELECT
    id, kilometers, date,
    kilometers - COALESCE(
            ( SELECT p.kilometers
              FROM mytable AS p
              WHERE ( p.date = m.date AND p.car_driver = m.car_driver
                                                     AND p.id < m.id
                   OR p.date = m.date AND p.car_driver < m.car_driver
                   OR p.date < m.date
                    )
              ORDER BY p.date DESC, p.car_driver DESC
                  LIMIT 1
            ), kilometers) 
        AS number_km_since_last_date
FROM
    mytable AS m ;

or a self-join (already provided by @Michael Fredrickson) or using MySQL variables (already provided as well).

If you want the counter to start again from 0 for every car_id, which would be done with PARTITION BY in many other DBMS:

SELECT
    id, kilometers, date,
    kilometers 
    - COALESCE( LAG(kilometers) OVER (PARTITION BY car_id 
                                      ORDER BY date ASC, car_driver ASC, id ASC)
              , kilometers) 
        AS number_km_since_last_date
FROM
    mytable ;

it could be done in MySQL like this:

SELECT
    id, kilometers, date,
    kilometers - COALESCE(
            ( SELECT p.kilometers
              FROM mytable AS p
              WHERE p.car_id = m.car_id
                AND ( p.date = m.date AND p.car_driver = m.car_driver
                                                     AND p.id < m.id
                   OR p.date = m.date AND p.car_driver < m.car_driver 
                   OR p.date < m.date
                    )
              ORDER BY p.date DESC, p.car_driver DESC
                  LIMIT 1
            ), kilometers) 
        AS number_km_since_last_date
FROM
    mytable AS m ;

@MichaelMcGowan Yeah, I was writing how it could be done in MySQL, but Michael was faster. — ypercubeᵀᴹ, Feb 13 '13 at 15:55
http://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/ shows a generic solution to getting lag() in MySQL — TML, Feb 13 '13 at 15:55
Thing is that I can have twice the same date. Hence why my ORDER BY is done on other criteria that just the date: car_id, car_driver, km etc... — user1108276, Feb 13 '13 at 16:11
Then you can change the `ORDER BY p.date DESC` part with what you need. — ypercubeᵀᴹ, Feb 13 '13 at 16:12
But how does it go to the next record? I see that you have `WHERE p.date < m.date` in my case dates may not be sorted correcty and you can have two entries on the same day... — user1108276, Feb 14 '13 at 10:11

Vitor Cruz · Answer 3 · 2022-05-04T20:27:59.567

1

It is a way to solve this using mySQL's window functions:

SELECT
id,
kilometers,
date,
car_id,
car_driver,
COALESCE(kilometers - LAST_VALUE(kilometers) OVER(PARTITION BY car_id, car_driver ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS number_km_since_last_date
FROM mytable
ORDER BY car_driver, car_id, date

edited May 04 '22 at 20:27

answered May 04 '22 at 20:22

Vitor Cruz

11
2

score 0 · Answer 4 · edited Feb 13 '13 at 16:24

With data unsorted I can only think of inline subquery (not a good idea on the large table):

select t1.*,
t1.Kilometers - (select top 1 kilometers from mytable t2 where t2.date < t1.date order by t2.date desc) as number_km_since_last_date
from mytable t1

If you get data sorted you can use left join

select t1.*
t1.Kilometers - t2.Kilometers as number_km_since_last_date
from mytable t1
left join mytable t2
  on t1.id = t2.id + 1

You can probably tell that I'm more of a TSQL guy so you might need to adjust syntax for MySQL.

Tarunpreet Ubhi · Answer 5 · 2017-02-23T00:49:23.347

Here's an example of using CURSOR for this use case as well

CREATE TABLE TEMP1
(
    MyDate DATETIME,
    MyQty INT
)

INSERT INTO TEMP1 VALUES ('01/08/17', 100)
INSERT INTO TEMP1 VALUES ('01/09/17', 120)
INSERT INTO TEMP1 VALUES ('01/10/17', 180)

DECLARE @LastDate DATETIME = NULL
DECLARE @LastQty INT = NULL
DECLARE @MyDate DATETIME = NULL
DECLARE @MyQty INT = NULL

DECLARE mycursor CURSOR FOR
SELECT MyDate, MyQty FROM TEMP1 ORDER BY MyDate
OPEN mycursor
FETCH NEXT FROM mycursor INTO @MyDate, @MyQty

WHILE @@FETCH_STATUS = 0  
BEGIN  

    SELECT @MyDate, @MyQty - @LastQty

    SET @LastDate = @MyDate
    SET @LastQty = @MyQty

FETCH NEXT FROM mycursor INTO @MyDate, @MyQty
END

CLOSE mycursor
DEALLOCATE mycursor

score 0 · Answer 6 · answered Apr 03 '20 at 07:52

With MySQL 8 you can use CTE and ROW_NUMBER window function to make a more readable query

WITH cte_name AS (
    SELECT
    ROW_NUMBER() OVER (ORDER BY update_time) as row_num,
    id,
    other_data,
    update_time
    FROM table_name WHERE condition = 'some_condition'
)
SELECT t2.id, t2.other_data, TIMEDIFF(t2.update_time, t1.update_time) AS time_taken
FROM
cte_name t1
JOIN cte_name t2 ON t1.row_num = t2.row_num-1
ORDER BY time_taken;

In this example I'm trying get the difference between datetime values.

The idea is to use ROW_NUMBER window function to assign an incremental number to each row after ordering by update_time.
The CTE allows us to write a subquery without having to repeat writing the same code.
We self join the CTE. The joining condition is basically - each nᵗʰ item of the second subquery joins with the n-1ᵗʰ item of the first subquery (this also means the first row will disappear from the result set. if you need it you can use a UNION to add the first row to the start).

There are some good tutorials for: CTE (Common Table Expression), ROW_NUMBER and even window functions

MySQL difference between two rows of a SELECT Statement

6 Answers6

Sql Fiddle

Sql Fiddle

Linked

Related