MySQL difference between two rows of a SELECT Statement

by 
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

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. MySQL INNER JOIN select only one row from second table
  3. What is better subquery vs literal on IN clause in mysql
  4. SQL query return data from multiple tables
  5. Select all columns except one in MySQL?
  6. How to join two tables mysql?
  7. MySQL SELECT only not null values
  8. How to use mysql JOIN without ON condition?
  9. MySQL SELECT increment counter
  10. MySQL select with CONCAT condition
  11. Difference in MySQL JOIN vs LEFT JOIN
  12. How does MySQL’s ORDER BY RAND() work?
  13. MySQL INSERT INTO … VALUES and SELECT
  14. Mysql select distinct
  15. Return a default value if single row is not found
  16. How to select one row randomly taking into account a weight?
  17. Compare only day and month with date field in mysql
  18. MySQL: Inner join vs Where [duplicate]
  19. Sum results of a few queries and then find top 5 in SQL
  20. MySQL SELECT LIKE or REGEXP to match multiple words in one record
  21. Syntax error when using except in a query
  22. Using index, using temporary, using filesort – how to fix this?
  23. What is the default MySQL JOIN behaviour, INNER or OUTER?
  24. MySQL : retrieve a large select by chunks
  25. MySQL – insert data from another table merged with constants
  26. Insert data into table with result from another select query
  27. How to SELECT by MAX(date)?
  28. How to use Table output from stored MYSQL Procedure
  29. MySQL matching unicode characters with ascii version
  30. MySQL Select Multiple VALUES

Leave a Comment