How to access values from previous or following rows in Oracle ?

OracleSoftware & CodingProgramming

You want to use Oracle aggregate function XMLAGG for string aggregation.?

Solution:

You would like to include calculations based on preceding and following rows in the result set.

Oracle supports the LAG and LEAD analytical functions to provide access to multiple rows in a table, utilizing preceding or following logic and you won’t need to resort to joining the source data to itself. To demonstrate the usage we will use the students data.

The LAG function can be used to see which student/s joining followed another, and also to calculate the elapsed time between joining.

SQL: Identify the student joining information

Example

SELECT first_name,       last_name,       join_date,       lag(join_date, 1, '01-JAN-2001') OVER (ORDER BY join_date)             AS prev_join_data,       join_date - lag(join_date, 1, '01-JAN-2001') OVER (ORDER BY join_date) AS join_date_diff FROM students ORDER BY join_date;

Output

|first_name | last_name | join_date | prev_join_data | joining_date_diff | ---       | ---        | ---       |  ---          |  --- | WILLIAMS   |ROBERT      |13/JAN/01      |01/JAN/01      |12 WILSON      |THOMAS      |16/AUG/02      |13/JAN/01      |580 RODRIGUEZ   |JOSEPH      |17/AUG/0      2|16/AUG/02      |1 SMITH      |JAMES      |17/JUN/03      |17/AUG/02      |304 MILLER      |DAVID      |25/JUN/05      |17/JUN/03      |739 JOHNSON      |JOHN      |21/SEP/05      |25/JUN/05      |88 MARTINEZ      |CHRISTOPHER|28/SEP/05      |21/SEP/05   |7 ANDERSON      |DANIEL      |30/SEP/05      |28/SEP/05   |2 BROWN      |MICHAEL      |03/JAN/06      |30/SEP/05   |95 DAVIS      |RICHARD      |05/FEB/06      |03/JAN/06   |33 TAYLOR      |PAUL      |07/MAR/06      |05/FEB/06   |30 GARCIA      |CHARLES      |07/FEB/07   |07/MAR/06   |337 JONES      |WILLIAM      |21/MAY/07   |07/FEB/07   |103 THOMAS      |MARK      |07/DEC/07   |21/MAY/07      |200

The LAG and LEAD functions are similar to most other analytical functions in that they operate once the non analytic portion of the query is complete. Oracle performs a second pass over the intermediate result set to apply any analytical predicates.

The LEAD function works the same way as LAG function, but instead tracks following rows rather than preceding ones.

To demonstrate, We will show joins along with the join_date of the next student, and a similar elapsed time window between their joindates.

Example

SELECT first_name  ,       last_name  ,       join_date   ,       lead(join_date, 1, sysdate) OVER (ORDER BY join_date)              AS next_join_date   ,       lead(join_date, 1, sysdate) OVER (ORDER BY join_date) -  join_date  AS days_between_join FROM students ORDER BY join_date

Output

first_name
last_name
join_date
next_join_date
days_between_join
WILLIAMS
ROBERT
13/JAN/01
16/AUG/02
580
WILSON
THOMAS
16/AUG/02
17/AUG/02
1
RODRIGUEZ
JOSEPH
17/AUG/02
17/JUN/03
304
SMITH
JAMES
17/JUN/03
25/JUN/05
739
MILLER
DAVID
25/JUN/05
21/SEP/05
88
JOHNSON
JOHN
21/SEP/05
28/SEP/05
7
MARTINEZ
CHRISTOPHER
28/SEP/05
30/SEP/05
2
ANDERSON
DANIEL
30/SEP/05
03/JAN/06
95
BROWN
MICHAEL
03/JAN/06
05/FEB/06
33
DAVIS
RICHARD
05/FEB/06
07/MAR/06
30
TAYLOR
PAUL
07/MAR/06
07/FEB/07
337
GARCIA
CHARLES
07/FEB/07
21/MAY/07
103
JONES
WILLIAM
21/MAY/07
07/DEC/07
200
THOMAS
MARK
07/DEC/07
27/NOV/20
4739.08978009259259259259259259259259259

Data Preparation: Data used for the problem is shown below.

Example

DROP TABLE students;
COMMIT;

CREATE TABLE students
    ( student_id     NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
    , email          VARCHAR2(40)
    , phone_number   VARCHAR2(20)
    , join_date      DATE
    , class_id       VARCHAR2(20)  
    , fees           NUMBER(8,2)
    , professor_id   NUMBER(6)
    , department_id  NUMBER(4)
    ) ;

Example

CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);

COMMIT;
raja
Updated on 05-Dec-2020 06:34:42

Advertisements