Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011

    Unanswered: How to pull the most recent order for each customer?

    I have a db that is full of orders, along with the customers name, address, etc. There are multiple entries for each customer because they have purchased things more than once. I need to export a table that has all the customers and only their most recent order. I figure I can use the `id` field because they are unique and the highest `id` means the most recent.

    I just need to filter out the most recently entered `id` along with the rest of the info in the fields all the way down the row for each customer.

    The table is named orders and the id is just called `id`

    Any ideas??

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    You need to make sure that your 'id' field is incremental and that the max per customer is indeed correct. Also how do you define a customer by its name and/or address? For example, a name may not be unique enough so you may also need to link in the address. However, differences in spelling of the address may cause issues with your results.

    Try the following (if name and address uniquely defines each customer):

    SELECT customerName, address, max(id)
    FROM orders
    GROUP BY customerName, address;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    the highest id field may effectively mean its the most recent, but you should not rely on an autogenerated column as having any other meaning that a means to make a row unique. what you should be doing is using a date time column when the order was placed.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts