MS SQL Server

SQL Server LAG Function

The lag function in SQL Server is a windows function introduced in SQL Server 2012. This function allows you to fetch the data of previous rows as per the specified offset value. Think of the lag function as the ability to access data from previous rows from the current row.

For example, from the current row, you can access the previous row, which becomes the current row, and you can access the previous row, and so on.

In this article, we will learn how to use the lag function in SQL Server using various examples.

SQL Server LAG() Function

We express the syntax of the function as:

lag(expression, offset [,DEFAULT])
OVER (
        [partition BY partition_by_expression]
        order_by_clause
)

Function Parameters and Return Value

In the syntax above, we have the following parameters:

  1. Expression – a column or expression used by the lag function to perform the calculations. This is a required parameter, and the expression must return a single value.
  2. Offset – a positive integer value that defines how many rows back the lag function will retrieve. If not specified, the default value is set to 1.
  3. Default – specifies the default value returned by the function if the specified offset value goes beyond the scope of the partition. By default, the function return NULL.
  4. Partition_by_expression – an expression used to create logical data partitions. SQL Server will apply the lag function to the resulting partition sets.
  5. Order_by_clause – an expression to define how the rows in the resulting partitions are ordered.

The function returns the data type of the scalar expression.

SQL Server Lag Examples

Let us look at a practical example to understand better how to use the lag function. Let us start by adding sample data as shown:

CREATE DATABASE sampledb;
GO
USE sampledb;
CREATE TABLE lag_func(
        id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
        dbname VARCHAR(50),
        paradigm VARCHAR(50),
);
INSERT INTO lag_func(dbname, paradigm)
VALUES ('MySQL', 'Relational'),
           ('MongoDB', 'Document'),
           ('Memcached', 'Key-Value Store'),
           ('Etcd', 'Key-Value Store'),
           ('Apache Cassandra', 'Wide Column'),
           ('CouchDB', 'Document'),
           ('PostgreSQL', 'Relational'),
           ('SQL Server', 'Relational'),
           ('neo4j', 'Graph'),
           ('Elasticsearch', 'Full-Text');
SELECT * FROM lag_func;

The above query set should return data as:

Run the lag function on the dbname column as shown in the example query below:

SELECT *, lag(dbname, 1) OVER (ORDER BY dbname) AS previous_db FROM lag_func;

The query above returns an output as:

Notice, the first row contains a null value, as it does not have a previous value.

Example 2:

Instead of getting a null value where the row does not contain a previous row, we can set a default value, as shown in the example query below:

SELECT dbname, lag(dbname, 1, 'N/A')
OVER (ORDER BY dbname) AS previous_db
FROM lag_func;

The query above returns a similar output as above. However, instead of NULL, we get the specified string.

Example 3: Custom Offset Value

We can also fetch the values on a custom offset value. For example, to get the value of three previous rows, we can use the query:

SELECT dbname, lag(dbname, 3, 'N/A')
OVER (ORDER BY dbname) AS previous_db
FROM lag_func;

The example code above should return the result as:

Here, the first 3 columns are null as the offset values go beyond the available rows’ scope.

Example 4: Partition By

We can create logical partitions of related data using the partition by clause. We can then apply the lag function to each partition.

Consider the example below:

SELECT dbname, paradigm, lag(dbname, 1, 'N/A')
OVER (partition BY paradigm ORDER BY dbname) AS previous_db
FROM lag_func;

The query above returns an example query set as:

The query creates 6 partitions based on the paradigm in the above result. On each partition, the lag function fetches the previous row.

Conclusion

This article taught you how to use the SQL Server lag function to retrieve the previous row from a resulting set.

Thank you for reading!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list