selecting dates from a table into two columns

Question

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 1

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