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.
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