Date Questions

[sophie123]

If you had a table that had a primary key.. say a ss number and you had one other field in your table that was a date field. What would be the easiest way to find the 2nd most recent date or the second oldest date in the table

 

[dnoeth]

traditional SQL:

select max(datecol)
from tab
where datecol < (select max(datecol) from tab)


OLAP-function:

select datecol
from tab
qualify rank() over (order by datecol desc) = 2

if datecol is not unique:

select datecol
from (select distinct datecol from tab) dt
qualify rank() over (order by datecol desc) = 2

Dieter