LAG function for previous row access in SQL

Introduction

The LAG function helps you look at the value from the previous row in a table. It is useful when you want to compare current data with past data in a list.

To find the difference between sales of this month and the previous month.

To compare a student's current test score with their previous test score.

To check if a stock price went up or down compared to the last day.

To see how a value changes over time in a list of events.

Syntax

SQL

LAG(column_name, offset, default_value) OVER (ORDER BY column_to_order)

The offset is how many rows back you want to look. If you leave it out, it looks 1 row back.

The default_value is what you get if there is no previous row (like the first row). It is optional.

Examples

Gets the sales from the previous month for each row ordered by month.

SQL

SELECT LAG(sales) OVER (ORDER BY month) AS prev_month_sales FROM sales_data;

Looks two tests back for the score. If no such test, returns 0.

SQL

SELECT LAG(score, 2, 0) OVER (ORDER BY test_date) AS score_two_tests_ago FROM tests;

Shows each employee's salary and the salary of the previous employee by ID.

SQL

SELECT employee, salary, LAG(salary) OVER (ORDER BY employee_id) AS prev_salary FROM employees;

Sample Program

This creates a sales table with monthly sales. Then it shows each month's sales and the previous month's sales.

SQL

CREATE TABLE sales_data (month VARCHAR(10), sales INT);
INSERT INTO sales_data VALUES
('Jan', 100),
('Feb', 150),
('Mar', 130),
('Apr', 170);

SELECT month, sales, LAG(sales) OVER (ORDER BY month) AS prev_month_sales FROM sales_data;

Important Notes

LAG does not change the order of rows; it just adds a column with previous row data.

If the previous row does not exist, LAG returns NULL or the default value you set.

Always use ORDER BY inside OVER() to define which row is previous.

Summary

LAG lets you see data from the previous row in a list.

It helps compare current and past values easily.

You must use ORDER BY to tell SQL how to find the previous row.