Skip to content
Advertisement

MySQL lag() with default value previous row value

Here is my sqlfiddle – http://sqlfiddle.com/#!9/fa7b9a/2 .

How I can get with LAG() previous and current it this for March results?

SELECT p6 as Current,lag(p6) over (ORDER BY dt) AS previous_val FROM dobridol
WHERE  dt BETWEEN '2021-03-01' AND '2021-03-30'

Also how I can set first value to have for previous_val last FEB result and then to go as per usual?

Advertisement

Answer

One of possible solutions – calculate LAG for whole table and after that filter results:

SELECT 
    p6,
    id,
    prev_val 
FROM (
  SELECT
    dt,
    p6,
    id,
    lag(p6) over (ORDER BY dt) AS prev_val 
  FROM dobridol
) tbl
WHERE dt BETWEEN '2021-03-01' AND '2021-03-30';

SQL fiddle

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement