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
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation