Home SQL LAG/LEAD: How to Get Previous and Next Rows?

Databases

SQL LAG/LEAD: How to Get Previous and Next Rows?

Learn how to retrieve SQL results with previous and next rows using LAG/LEAD or alternatives. Handle edge cases and optimize performance in SQLite.

byDev Solutions

February 11, 2026

Stylized SQL LAG and LEAD thumbnail showing previous and next row comparison using arrows on a data table

📈 SQL LAG and LEAD functions make it easier to get previous and next row values for trend or time-series analysis.
⚡ Window functions like LAG() and LEAD() work better and are easier to read than old self joins or subqueries.
🔄 You need ORDER BY in window functions. Without it, LAG/LEAD results may not be dependable or can be random.
🧩 You can use workarounds, such as self joins and correlated subqueries, if native LAG/LEAD is not available.
🚀 Good indexing and partitioning make SQL previous and next row queries much faster.

You often need to get values from the row before or after in SQL. This is true for many analysis jobs, like comparing old and new values, watching trends, or figuring out how things change over time. People used to do this with complex subqueries or joins. But now, SQL window functions like LAG() and LEAD() solve this well. This guide will cover everything you need to know about these functions. We will look at their syntax, ways to make them faster, how to deal with tricky situations, and what to do if your system does not support them. And then we will look at more uses, like finding changes and measuring differences.

What Are LAG() and LEAD() in SQL?

LAG() and LEAD() are SQL window functions. They let you do calculations on groups of table rows that are linked to the current row. These functions let you get values from a row that comes before (LAG) or after (LEAD) the current row in your results.

This is very useful for looking at data that has a time order or sequence. For example, you can see financial trends, how numbers change over time, or compare different versions of records.

Syntax Breakdown

LAG(column_name [, offset [, default_value]]) 
  OVER (PARTITION BY partition_column ORDER BY order_column)

LEAD(column_name [, offset [, default_value]]) 
  OVER (PARTITION BY partition_column ORDER BY order_column)

Here is what each part means:

column_name: This is the column you want the previous or next value from.
offset (optional): This is a whole number. It tells the function how many rows to look back (LAG) or forward (LEAD). It defaults to 1.
default_value (optional): If there is no row to look at, this is the value the function will return.
PARTITION BY (optional): This splits your data into groups. The function then works separately on each group.
ORDER BY: This sets the order of rows in each group. It is very important for telling what "previous" and "next" mean.

Classic Use Example

SELECT 
  employee_id,
  salary,
  LAG(salary) OVER (ORDER BY employee_id) AS previous_salary,
  LEAD(salary) OVER (ORDER BY employee_id) AS next_salary
FROM employees;

This query makes two new columns. One column shows each employee’s salary from the employee with the ID before them. The other column shows the salary from the employee with the next ID.

Why Use LAG() and LEAD()? Real-World Use Cases

Getting previous and next rows using LAG and LEAD is now very important for analysis. It gives good ways to handle rows one after another.

Business & Practical Uses

📊 Stock Price Comparisons
Quickly see how prices change each day. Find gains and trends.
🕵️ Audit Trail Comparisons
Get the record's state before a change or event happened.
📦 Inventory Shifts
See when supplies went up or down by looking at nearby records.
🌍 User Activity Tracking
See how many people visited your site or app each day. Compare this to past days.
💼 Employee Performance
Compare how employees perform each month or how many tasks they finish.

These examples show that SQL LAG and LEAD make queries simpler. And they also make applications run better and easier to keep up.

SQL Previous and Next Row Use Case: Example Deep Dive

Let’s look at a table that tracks employee hours:

+----+------------+--------+
| ID | Log_Date   | Hours  |
+----+------------+--------+
| 1  | 2023-01-01 |  8.0   |
| 2  | 2023-01-02 |  7.5   |
| 3  | 2023-01-03 |  9.0   |
+----+------------+--------+

Applying LAG and LEAD

SELECT 
  ID,
  Log_Date,
  Hours,
  LAG(Hours) OVER (ORDER BY Log_Date) AS Previous_Hours,
  LEAD(Hours) OVER (ORDER BY Log_Date) AS Next_Hours
FROM time_logs;

Output:

+----+------------+--------+----------------+------------+
| ID | Log_Date   | Hours  | Previous_Hours | Next_Hours |
+----+------------+--------+----------------+------------+
| 1  | 2023-01-01 |  8.0   | NULL           | 7.5        |
| 2  | 2023-01-02 |  7.5   | 8.0            | 9.0        |
| 3  | 2023-01-03 |  9.0   | 7.5            | NULL       |
+----+------------+--------+----------------+------------+

Here, LAG() gets the hours from the date before. And LEAD() gets the hours from the next date.

Benefits Over Traditional Self Joins

Before, people often used self joins or correlated subqueries to get previous or next rows. But you should not use them.

Drawbacks of Self Joins

SELECT 
  t1.ID, 
  t1.Log_Date, 
  t1.Hours,
  t2.Hours AS Previous_Hours
FROM time_logs t1
LEFT JOIN time_logs t2 
  ON t2.Log_Date = (
    SELECT MAX(Log_Date) 
    FROM time_logs 
    WHERE Log_Date < t1.Log_Date);

This is harder to write, test, and keep up. The window function version is:

SELECT 
  ID, 
  Log_Date, 
  Hours,
  LAG(Hours) OVER (ORDER BY Log_Date) AS Previous_Hours
FROM time_logs;

The query has fewer lines. It has no subqueries or joins. This makes it easier to read, faster to run, and simpler to fix errors.

Understanding ORDER BY in OVER Clause

How well SQL LAG and LEAD work depends on the ORDER BY clause.

With ORDER BY:

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

This gives results you can count on. It controls the order of rows.

Without ORDER BY:

-- Unreliable!
SELECT LAG(salary) OVER () 
FROM employees;

The order of the results can be random. Always set the order of rows when you use window functions.

Managing NULL Returns and Edge Rows

LAG() and LEAD() return NULL by default if there is no previous or next row. This happens with the first or last row.

Set Default Values

You can handle these situations by using the third part of the function:

LAG(salary, 1, 0) OVER (ORDER BY employee_id) AS previous_salary

This sets the salary to 0 if there is no data from before.

Conditional Logic with CASE

SELECT 
  salary,
  LAG(salary) OVER (ORDER BY employee_id) AS prev,
  CASE 
    WHEN salary > LAG(salary) OVER (ORDER BY employee_id) THEN 'Increase'
    ELSE 'Decrease or Same'
  END AS Trend
FROM employees;

This lets you set up specific rules. For example, you can find the direction of a change or set triggers.

What if SQL LAG LEAD Is Unsupported?

Some older database systems, like SQLite before version 3.25 or MySQL before version 8.0, may not work with LAG() and LEAD(). Here are other ways to do it:

Old SQLite: Self Join Approach

SELECT 
  curr.ID,
  curr.value,
  prev.value AS previous_value
FROM table_name AS curr
LEFT JOIN table_name AS prev
  ON prev.ID = (
    SELECT MAX(ID)
    FROM table_name
    WHERE ID < curr.ID);

This works much the same, but it can be slower.

MySQL 5.x:

SELECT 
  a.id,
  a.value,
  (SELECT value FROM table_name b WHERE b.id < a.id ORDER BY b.id DESC LIMIT 1) AS previous_value
FROM table_name a;

Subqueries within SELECT statements run slower on big datasets. But they are useful when you cannot use window functions.

SQL Server: CROSS APPLY Hack

SELECT a.id, a.value, b.value AS prev_value
FROM table_name a
OUTER APPLY (
  SELECT TOP 1 value 
  FROM table_name b 
  WHERE b.id < a.id 
  ORDER BY id DESC
) b

This acts like LAG. It uses features specific to SQL Server.

Alternative Techniques to Access Adjacent Rows

If you do not have LAG/LEAD, or if you have specific needs, you can use these methods to get nearby rows:

Correlated Subqueries

These are inner queries that look at values from the outer query.

Inequality Joins

SELECT a.*, b.value AS previous
FROM table a
LEFT JOIN table b ON b.id = (
  SELECT MAX(id) FROM table WHERE id < a.id);

ROW_NUMBER with Joins

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM table
)
SELECT a.*, b.*
FROM ranked a
LEFT JOIN ranked b ON a.rn = b.rn + 1;

JOINs can set positions using ROW_NUMBER.

Optimizing "Get Next Row SQL" Queries

Even with built-in LAG or LEAD, you need to make queries run faster when you have large amounts of data.

Ways to Make Queries Faster:

✅ Proper Indexing:
Index the ORDER BY and PARTITION BY columns. This will make the database scan less data.
✅ Minimize Partitioning Scope:
Only split data into groups when you need to compare things inside those groups, like departments or regions.
✅ Selective Projections:
Do not select all columns from your table. Only pick the ones your query actually needs.
✅ Materialize CTEs (If Supported):
Many database systems can turn CTEs into temporary views. This makes them process faster.

Debugging & Troubleshooting Common Issues

Developers often run into problems when they work with SQL logic for previous and next rows. Here is how to fix them:

❌ Unexpected NULLs?
This can happen because of order problems or if nearby rows are missing. Use default values or CASE logic.
❌ Incorrect Sequencing?
Always check the ORDER BY column again.
❌ Partitioning Breaks Flow?
Check if your PARTITION BY clause is splitting rows too much.
✅ Best Practice:
Start simple. Test queries on small parts of your data first. Then use them on huge amounts of data.

Advanced Application: Calculating Deltas and Percent Changes

A very strong use for SQL LAG/LEAD is figuring out how things change over time.

Sample: Metric Trend Over Time

SELECT 
  date,
  cpu_usage,
  LAG(cpu_usage) OVER (ORDER BY date) AS prev_cpu,
  cpu_usage - LAG(cpu_usage) OVER (ORDER BY date) AS delta_cpu,
  ROUND(
    ((cpu_usage - LAG(cpu_usage) OVER (ORDER BY date)) / 
      LAG(cpu_usage) OVER (ORDER BY date)) * 100, 2
  ) AS percent_change
FROM metrics;

This helps a lot with dashboards, watching performance, or looking at key numbers.

Final Thoughts: When and Why to Use SQL LAG and LEAD

SQL LAG and LEAD functions are key tools for analysis queries. They get rid of the need for complex joins or subqueries. And they run much better and are easier to read.

Best Practices Summary

✅ Always set ORDER BY to make the order clear.
✅ Use default_value to deal with NULL rows well.
✅ Avoid unnecessary partitioning unless needed.
✅ Look for other ways to do things if your database does not support these functions.
✅ Use them with CASE, math, or other functions to get more understanding.

LAG and LEAD are easy to use and work in many ways. So, every analyst and engineer should know how to use them and get next row SQL.

Citations