MySQL SQL order join for multiple records

Question

So I have 2 tables:

1. Customers
2. Files

How do I do a join to show the latest record, i.e by created_on DESC

e.g

SELECT customers.name, files.last_file_submission

if I have:

customer A
 - File 1, created_on 10-10-2012
 - File 2, created_on 11-10-2012

and I want to return a single line for each customer that has:

customer A, 11-10-2012

Answer 1

It will bring all the Customer and their latest file date regardless if the customer has any record of file or not.

SELECT DISTINCT customer.id, customer.name, MAX(file.submit_date)
FROM customer LEFT JOIN file ON customer.id = file.submitter
GROUP BY customer.id, customer.name

You can change LEFT with INNER to restrict the join only between those customer who have the reference on file table.

Answer 2

Per file:

SELECT c.name, f.submit_date
FROM files f, customers c
WHERE f.submitter = c.id
ORDER BY f.submit_date DESC

Per user:

SELECT DISTINCT c.name, MAX(f.submit_date)
FROM files f, customers c
WHERE f.submitter = c.id
ORDER BY MAX(f.submit_date) DESC
GROUP BY c.id