mHelpMe mHelpMe - 9 months ago 33
SQL Question

selecting dates from a table into two columns

I have a table and one of the fields is a date field.

I have been asked to write a query that returns a list of distinct dates (ordered) in say column A and then have another column, say B of dates where the date in column B is the greatest date that is less than column A.

MyDateField

2017-01-01
2017-01-01
2017-01-01
2017-01-02
2017-01-02
2017-01-03
2017-01-04
2017-01-05
2017-01-05
2017-01-05


Answer required

2017-01-05 2017-01-04
2017-01-04 2017-01-03
2017-01-03 2017-01-02
2017-01-02 2017-01-01
2017-01-01

Answer Source

If you're using SQL-Server 2012+, then you can use LAG() to fetch the last biggest date out of the table:

SELECT t.date,
       LAG(t.date,1) OVER(ORDER BY t.date) as last_biggest_date
FROM (SELECT DISTINCT s.dateField FROM YourTable s) t
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download