This guide is specifically for MySQL syntax.

How to Compare Current Row with Previous in SQL

Access previous or next row values using LAG and LEAD window functions.

Quick Answer

SELECT date, value, LAG(value) OVER (ORDER BY date) as prev_value FROM data;

Explanation

LAG accesses the previous row's value. LEAD accesses the next row's value. Both accept an offset parameter (default 1) and optional default value.

Query Variants

Lag

SELECT
  sale_date,
  revenue,
  LAG(revenue) OVER (ORDER BY sale_date) as prev_day,
  revenue - LAG(revenue) OVER (ORDER BY sale_date) as change
FROM daily_sales;

Lead

SELECT
  sale_date,
  revenue,
  LEAD(revenue) OVER (ORDER BY sale_date) as next_day
FROM daily_sales;

Growth Rate

SELECT
  sale_date,
  revenue,
  ROUND((revenue - LAG(revenue) OVER (ORDER BY sale_date)) * 100.0 / 
    LAG(revenue) OVER (ORDER BY sale_date), 2) as growth_pct
FROM daily_sales;

Pro Tips

• LAG looks back, LEAD looks forward
• Handle NULL for first/last rows
• Great for calculating changes/trends

Related SQL Queries

Continue learning with more SQL query examples:

MySQL-Specific Notes

This page covers MySQL syntax. Other databases may have different syntax for similar operations.