5

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???

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
sam
  • 229
  • 1
  • 4
  • 8
  • 1
    I think there's no built-in function. You would probably have to JOIN same table in order get such effect. Try something from this thread: http://stackoverflow.com/questions/5483319/how-do-i-lag-columns-in-mysql or http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – Jan.J Aug 26 '13 at 06:14
  • 1
    No, MySQL does not support any of the modern SQL features (window functions, recursive queries, ...). If that is really important for you, you might consider upgrading to Postgres –  Aug 26 '13 at 06:53

1 Answers1

6

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

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.

fancyPants
  • 50,732
  • 33
  • 89
  • 96