Posted By

bbrumm on 02/20/17


Tagged

sql Oracle


Versions (?)

Oracle LEAD Function


 / Published in: SQL
 

URL: http://www.databasestar.com/oracle-lead/

A few examples of the LEAD function.

  1. SELECT * FROM student;
  2.  
  3. --Example 1
  4. SELECT student_id, first_name, last_name, enrolment_date,
  5. LEAD(enrolment_date) OVER (ORDER BY enrolment_date ASC) AS next_enr_date
  6. FROM student
  7. ORDER BY student_id ASC;
  8.  
  9. --Example 2
  10. SELECT student_id, first_name, last_name, enrolment_date,
  11. LEAD(enrolment_date) OVER (ORDER BY enrolment_date ASC) AS next_enr_date
  12. FROM student
  13. ORDER BY enrolment_date ASC;
  14.  
  15. --Example 3
  16. SELECT student_id, first_name, last_name, enrolment_date,
  17. LEAD(enrolment_date, 2, '31-DEC-2999') OVER (ORDER BY enrolment_date ASC) AS next_enr_date
  18. FROM student
  19. ORDER BY enrolment_date ASC;
  20.  
  21.  
  22. --Example 3
  23. SELECT student_id, first_name, last_name, enrolment_date,
  24. LEAD(enrolment_date) OVER (ORDER BY enrolment_date ASC) AS next_enr_date
  25. FROM student
  26. WHERE LEAD(enrolment_date) OVER (ORDER BY enrolment_date ASC) > '01-FEB-2015'
  27. ORDER BY enrolment_date ASC;

Report this snippet  

You need to login to post a comment.