How Can I Access the Previous Row's Value in a SQL SELECT Statement?

Retrieving Previous Row Data in SQL SELECT Statements

Many SQL queries require accessing data from the preceding row for calculations or comparisons. This article demonstrates how to accomplish this in Microsoft SQL Server 2008 using the LAG function.

Leveraging the LAG Function

The LAG function efficiently retrieves values from a specified column in a previous row. Its syntax is:

<code class="language-sql">LAG(column_name, offset) OVER (ORDER BY order_column)</code>

Here's a breakdown:

• column_name: The column containing the value you need.
• offset: The number of rows to offset from the current row. A negative offset (e.g., -1) accesses the previous row.
• order_column: The column defining the row order for the calculation.

Illustrative Example

To compute the difference between consecutive rows in a column named value, use LAG as follows:

<code class="language-sql">SELECT value - LAG(value, 1) OVER (ORDER BY Id) AS ValueDifference
FROM your_table;</code>

This query subtracts the value from the previous row (obtained via LAG(value, 1)) from the current row's value, producing the difference. ORDER BY Id ensures the correct row order for the calculation.

Addressing Gaps in Sequence Numbers

It's crucial to remember that ID sequences might contain gaps. Directly referencing Id - 1 to find the previous row is unreliable in such cases. The LAG function, however, handles these gaps seamlessly, providing a robust solution.

The above is the detailed content of How Can I Access the Previous Row's Value in a SQL SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!