LEAD and LAG functions and examples (ORACLE & SQL SERVER)

Introduced in SQL SERVER 2012, the lead and lag functions is a long time friend from ORACLE users, and I definetely envied them for this. =)

Imagine you would need the value of the previous or next row from a especific column. Using a subquery could be a messy business and performance would be an issue (althought lead and lag are not garantee of blazing performance).

Instead of the "good old " subquery approach, try lead and lag.

LAG ( expression [, offset [, default] ] )

Expression is the column you want to access, offset is how many rows you want to go foward or backwars and default is the value you want to show case the columns value is null.

Here is some sample code to get you started:

DROP TABLE STUDENTS;

CREATE TABLE STUDENTS (
  NAME varchar(100),
  PRESENCES integer,
  SCORE integer
);

INSERT INTO STUDENTS  VALUES ('Rodrigo', 1, 5);
INSERT INTO STUDENTS  VALUES ('Daniel', 2, 6);
INSERT INTO STUDENTS  VALUES ('Ana', 3, 7);
INSERT INTO STUDENTS  VALUES ('Aline', 4, 8);

SELECT
DISTINCT
  NAME,
  PRESENCES,
  LAG(PRESENCES, 1, NULL) 
     OVER (ORDER BY NAME) AS "PREVIOUS PRESENCE",
  SCORE,
  LEAD(SCORE, 1, NULL) 
     OVER (ORDER BY NAME) AS "NEXT SCORE"
FROM STUDENTS
ORDER BY 1


Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06