This guide is specifically for SQL Server syntax.

SQL Query to Compare Current Row with Previous

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:

SQL Server-Specific Notes

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