CODEWITHSUNDEEP
mysqljoingroup-bysql-order-by
caw
caw

Reputation: 31487

Sort grouped rows in MySQL statement

I have a MySQL database with the following structure:

Table customers:

Table orders:

Now I want to select all customers as well as their latest order. I tried the following statement:

SELECT a.id, a.name, b.timestamp_unix, b.title FROM customers AS a JOIN orders AS b ON a.id = b.customerID GROUP BY a.id

This works fine except that I don't get the latest order (and its title) but the first one that has been inserted to the database as the first one.

So how can I get the latest order (highest id and highest timestamp_unix)? For the timestamp only, I could just use MAX(b.timestamp_unix) but how do I get the matching b.title?

Thank you!

Upvotes: 0

Views: 201

Answers (3)

Pritom
Pritom

Reputation: 1333

You can pls try this one, I have tested it well.

SELECT a.name as 'Customer Name', b.title as 'Order Title' FROM customers a, orders b where a.id=b.customerID AND b.timestamp_unix=(Select max(c.timestamp_unix) from orders c where c.customerID=a.id) GROUP BY a.id

Upvotes: 3

Charleh
Charleh

Reputation: 14002

You should do a subselect, do your join but take the grouping away, then make this what you join on

 Left join (select orderid from orders where customerid = A.customerid order by orderdate desc limit 1) as lastorder

I'd like to be more clear but I'm on my mobile haha

Here I'm on my pc now, here's a MSSQL fiddle to show it - just convert to Mysql (Syntax should be the same apart from the TOP 1 should be a LIMIT 1 at the end instead)

http://sqlfiddle.com/#!3/29a3c/13

Upvotes: 1

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT a.id, a.name, b.timestamp_unix, b.title 
FROM customers AS a 
JOIN (  SELECT customerID, timestamp_unix, title
        FROM orders
        ORDER BY timestamp_unix DESC) AS b
ON a.id = b.customerID 
GROUP BY a.id
ORDER BY timestamp_unix DESC

Read this question for more information

As mentioned in refered question, there are at least 2 approaches to solve this matter. Choose the one you find safiest and easiest.

Upvotes: 2

Related Questions