Display DISTINCT records with MAX date (MySQL)

Question

I have a simple retail database, with customer table, and orders table. Each order has a date, and each order is tied via ID to customer, so customers appear multiple times in the order table.

I'm trying to make a query which displays the customers details and the date of their last purchase, as well as days sinse last purchase.

I currently have:

SELECT customer.CustomerID, customer.Firstname, customer.LastName, orders.DateOrdered AS LastOrdered, DATEDIFF(CURDATE(), orders.DateOrdered) AS DaysSinseOrdered
FROM customer
JOIN orders ON customer.CustomerID = orders.CustomerID
ORDER BY DaysSinseOrdered DESC

This works fine, however is displays a record for each record in the orders table, so custoemrs appear multiple tiems in it, and the DATEDIFF is from that order, not last order. Which is what this query will of-course do.

However when I try doing:

MAX(orders.DateOrdered)

It just returns one record, that of the latest order, also if I do:

SELECT DISTINCT customer.CustomerID

It doesn't change, it still displays customers multiple times.

I'm still new to all this, so any help is appreciated -Tom

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html — Strawberry, Dec 02 '13 at 13:57

score 0 · Accepted Answer · answered Dec 02 '13 at 13:58

You need a GROUP BY clause, and use MAX everytime you need the last order date.

SELECT 
  c.CustomerID, 
  c.Firstname, 
  c.LastName, 
  MAX(o.DateOrdered) AS LastOrdered, 
  DATEDIFF(CURDATE(), MAX(o.DateOrdered)) AS DaysSinceOrdered
FROM customer c
JOIN orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Firstname, c.LastName

Display DISTINCT records with MAX date (MySQL)

1 Answers1