Using the LAG Function in SQL Server

The LAG function in SQL Server allows you to work with a row of data as well as the previous row of data in a data set. When would that ever be useful? If you’re a sports fan, you’re familiar with this concept whether you realize it or not. Let’s look at an example.

Need for Speed

Our example will deal with drivers and race lap times. First, we’ll create a table to hold our lap times and add some test data:

CREATE TABLE Lap
(
	Id INT IDENTITY(1,1) NOT NULL,
	Driver VARCHAR(100) NOT NULL,
	LapTime TIME NOT NULL
);
GO

INSERT INTO Lap
VALUES ('Max','00:01:50')
	,('Lewis','00:01:54')
	,('Sergio','00:01:55')
	,('Lando','00:01:58')
	,('Valtteri','00:02:00');
GO

If we’re following a race, we may see the drivers and current race times like this:

SELECT * FROM Lap;
GO

Where LAG can be used is to see how far a driver is “lagging” behind the driver ahead of him. This can be helpful in a couple of ways. We can use LAG to see both times in the same row:

SELECT Id, Driver, LapTime,
  LAG(LapTime) OVER(ORDER BY LapTime) as 'LagTime'
FROM Lap;
GO

In our example, we see NULL for LagTime in the first row because that is our race leader. We will see his LapTime again on the second row as the LagTime, the second place time on the third row as the LagTime, and so on down the table.

Every Second Counts

In our scenario, we can make this more readable and more familiar with what you would see on race day. We can use DATEDIFF to get the difference in times and display it in seconds.

SELECT Id, Driver, LapTime,
  DATEDIFF(s, LapTime,(LAG(LapTime) OVER(ORDER BY LapTime))) as 'Seconds_Behind'
FROM Lap;
GO

Once again, our first row is NULL because that is the leader. Looking at the second row, we see Lewis is 4 seconds behind Max. If we look at the third row, we see Sergio is 1 second behind Lewis.

League Leader

If you’re looking at any sports standings, you might see something similar. This concept can be used to show Games Back in sports like baseball and basketball.

Thanks for reading!