How to get the differences between two dates in Oracle which were in same column?

Question

I need the difference between two dates in Oracle in terms of days in which the dates were in the same column. That is, the difference between two dates after order by that column. That is, after doing the order by, I need the difference between first two dates.

Answer 1

You can use the LEAD/LAG OVER analytic functions.

Perhaps something like:

SELECT 
  TheDate CurrentDate, 
  LAG(TheDate, 1, 0) OVER (ORDER BY TheDate) PriorDate,
  TheDate - LAG(TheDate, 1, 0) OVER (ORDER BY TheDate) Difference
FROM SomeTable