Post
Ask Question

Fetch Previous Row Value With Lag Function And Without Lag Function

Introduction 

In this article, we will learn how to use the Lag function in SQL to get the preceding row value. We'll also look at how to get previous row values in SQL without using the lag function. In many interviews, interviewers ask candidates if they can find the previous row value in SQL. The majority of applicants respond quickly to the use of the Lag function. To add a twist, the interviewers ask candidates if they can build this solution without using the lag function. I see candidates struggling to find the correct answer. This article will show how to retrieve previous row values in SQL using the Lag function and without using the Lag function.

Lag Function in SQL

SQL Server 2012 onwards, it's a window function. Here we use the Lag () function to get data from previous rows based on an offset value. We can access earlier rows by using the Lag function. It's a handy tool for comparing current and previous row values.

Fetch Previous Row Value With Lag Function 

I hope you understand the problem statement. I have created a table with four columns: id, name, stack, and salary. I would like you to concentrate on id as part of this article. It is a column of sequential numbers such as 1,2,3, and so on. And I have to figure out what the preceding row value for the id column was for each row.

CREATE DATABASE emp_deatails;

CREATE TABLE details (
  id int,
  name varchar(50),
  stack varchar(50),
  salary int
);

INSERT INTO details
  VALUES (1, 'ishika', 'Graphite GTC', 100);
INSERT INTO details
  VALUES (2, 'deepak', 'CMS', 2000);
INSERT INTO details
  VALUES (3, 'nitin', 'Unity', 40000);
INSERT INTO details
  VALUES (4, 'abhishek', 'IOS', 60000);
INSERT INTO details
  VALUES (5, 'vijay', '.NET', 80000);
INSERT INTO details
  VALUES (6, 'Vijay kumari ', 'Hiring', 90000);

SELECT * FROM details;

Output

SELECT
  id,
  LAG(id) OVER (ORDER BY id) AS previous_id,
  name,
  stack,
  salary
FROM details;

Let me start with it; first will use the lag function. Whenever you are retrieving any previous or the next row, your data set should be sorted in a specific manner. First, we will use the Lag of id. So, in this case, let me order by id itself and name this column as a previous_id; now, this is my previous_id. Let me run this query and see the output shown in the snapshot below.

I have got the result. For id=1, no previous_id value exists. Because of this, this column has a value of NULL. This indicates that this id has never had a value before. In the same way, the previous_id value for id=2 is 1. It signifies that this column preceding row value was 1, which is correct. Similarly, the previous_id value of id=3 is 2, and the value before was 2. So, by using the lag function, you can quickly retrieve the preceding row data.

Fetch Previous Row Value Without Lag Function

In this case, you can use either min or max, and everything will stay the same. There's no need to be concerned with the order by clause or anything else; all you have to do now is provide additional information. We'll add rows between, then add one preceding and 1 proceeding. This is doing is pointing to the previous row. This is a reference to the preceding rule. Let's execute the query and see what comes back. So, as you can see, we got the same record for 1. There is a null in the previous record. For 2, we get 1 for 3, we get 2, and for 4, we get 3. So on

SELECT
  id,
  MIN(id) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS previous_id,
  name,
  stack,
  salary
FROM details;

output

Conclusion 

Here, we used the Lag function to access previous row data per the defined set value without using self-join. It helps compare the current row value with the previous row value. So basically, it is a helpful comparison of the current and previous values. Also, see above to fetch the previous row value without using the Lag function.

Thanks, I hope this will help you.


Similar Articles