sql - how to do lag operation in mysql

Question

Guys I want to use analytical function lag in mysql. In Oracle it is supported but I can't do it in Mysql. So can anybody help me how to perform lag operation in Mysql? For example

UID                        Operation
 1                         Logged-in
 2                         View content
 3                         Review

I want to use lag function so that my output would be as follows

UID                        Operation              Lagoperation
 1                         Logged-in                
 2                         View content           Logged-in
 3                         Review                 View content

Does Mysql support lag function???

Answer 1

You can emulate it with user variables:

select uid, operation, previous_operation from (
select
y.*
, @prev AS previous_Operation
, @prev := Operation
from
your_table y
, (select @prev:=NULL) vars
order by uid
) subquery_alias

• see it working in an sqlfiddle live

Here you initialize your variable(s). It's the same as writing SET @prev:=NULL; before writing your query.

, (select @prev:=NULL) vars

Then the order of these statements in the select clause is important:

, @prev AS previous_Operation
, @prev := Operation

The first just displays the variables value, the second assigns the value of the current row to the variable.

It's also important to have an ORDER BY clause, as the output is otherwise not deterministic.

All this is put into a subquery just out of aesthetic reasons,... to filter out this

, @prev := Operation

column.