By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,961 Members | 1,456 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,961 IT Pros & Developers. It's quick & easy.

Last order made by customer from a table

P: 1
I have 500 customers and 50 type of products with 20 colours each and 5 to 6 type of designs for each product. I want to retrieve data for each customer last order details. Fields in table are
C_Name P_Name Colour Designs Order date
Jun 25 '15 #1
Share this Question
Share on Google+
1 Reply

Seth Schrock
Expert 2.5K+
P: 2,951
The following query will get the last order by customer. If the field Order date is just a date field and the customer makes two orders on the same day, both orders will be included.
Expand|Select|Wrap|Line Numbers
  1. SELECT Lst.C_Name
  2.     , Table_Name.P_Name
  3.     , Table_Name.Colour
  4.     , Table_Name.Designs
  5.     , Lst.[Order date]
  6. FROM
  7.     (SELECT C_Name, Max([Order date]) As LastOrder
  8.     FROM Table_Name
  9.     GROUP BY C_Name) As Lst INNER JOIN Table_Name
  10.     ON Lst.C_Name = Table_Name.C_Name 
  11.         And Lst.LastOrder = Table_Name.[Order date]
Jun 25 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.