How do you write a query that will return a table with all the customers and a single order for each customer (along with pertinent order info) made closest to a certain specified date? I tried to write a query to return the most recent data (easiest case) and got an error message (I am new to SQL).
select MAX(OrderDate), Name, Address, Price from Orders
This is almost, but not quite, the same as Joining two tables but including the most recent row for each 26 April 2001.
What's different this time is that you want the closest date to a specified date, not the latest date. This might mean closest either before or after which would require a DATEDIFF function and an ABS value -- but I'll assume you want the closest before the specified date. This is simpler, and allows the use of MAX() with an additional WHERE condition --
select * from Customers , Orders where Customers.ID = Orders.Cust_ID and Orders.OrderDate = ( select max(OrderDate) from Orders where Cust_ID = Customers.ID and OrderDate < somedate )
This was first published in March 2002