Simulate lag function in MySQL

| time                | company | quote |
| 0000-00-00 00:00:00 | GOOGLE  |    40 |
| 2012-07-02 21:28:05 | GOOGLE  |    60 |
| 2012-07-02 21:28:51 | SAP     |    60 |
| 2012-07-02 21:29:05 | SAP     |    20 |

How do I do a lag on this table in MySQL to print the difference in quotes, for example:

SAP    | 40


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This is my favorite MySQL hack.

This is how you emulate the lag function:

SET @quot=-1;
select time,company,@quot lag_quote, @quot:=quote curr_quote
  from stocks order by company,time;
  • lag_quote holds the value of previous row’s quote. For the first row @quot is -1.
  • curr_quote holds the value of current row’s quote.


  1. order by clause is important here just like it is in a regular
    window function.
  2. You might also want to use lag for company just to be sure that you are computing difference in quotes of the same company.
  3. You can also implement row counters in the same way @cnt:[email protected]+1

The nice thing about this scheme is that is computationally very lean compared to some other approaches like using aggregate functions, stored procedures or processing data in application server.


Now coming to your question of getting result in the format you mentioned:

SET @quot=0,@latest=0,company='';
select B.* from (
select A.time,A.change,IF(@comp<>,1,0) as LATEST, as company from (
select time,company,<a href="" class="__cf_email__" data-cfemail="1b6a6e746f7e365b6a6e746f">[email protected]</a> as change, @quot:=quote curr_quote
from stocks order by company,time) A
order by company,time desc) B where B.LATEST=1;

The nesting is not co-related so not as bad (computationally) as it looks (syntactically) 🙂

Let me know if you need any help with this.

Method 2

From MySQL 8.0 and above there is no need to simulate LAG. It is natively supported,

Window Function :

Returns the value of expr from the row that lags (precedes) the current row by N rows within its partition. If there is no such row, the return value is default. For example, if N is 3, the return value is default for the first two rows. If N or default are missing, the defaults are 1 and NULL, respectively.

     LAG(quote) OVER(PARTITION BY company ORDER BY time) AS prev_quote
FROM tab;

DBFiddle Demo

Method 3

To achieve the desired result, first you need to find the last and next to last timestamps for each company. It is quite simple with the following query:

SELECT, c.mts, max(l.ts) AS lts
  FROM (SELECT company, max(ts) AS mts FROM cq GROUP BY company) AS c
  LEFT JOIN cq l
    ON = AND c.mts > l.ts
 GROUP BY, c.mts;

Now you have to join this subquery with the original table to get the desired results:

SELECT, l.quote, coalesce(l1.quote, 0),
       (l.quote - coalesce(l1.quote, 0)) AS result
  FROM (SELECT, c.mts, max(l.ts) AS lts
      FROM (SELECT company, max(ts) AS mts FROM cq GROUP BY company) AS c
      LEFT JOIN cq l
        ON = AND c.mts > l.ts
     GROUP BY, c.mts) AS c
  LEFT JOIN cq AS l ON = AND l.ts = c.mts
  LEFT JOIN cq AS l1 ON = AND l1.ts = c.lts;

You can observe results on SQL Fiddle.

This query is using only standard SQL capabilities and should work on any RDBMS.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x