CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
undefinedman
undefinedman

Reputation: 670

Order by desc in MySQL

I have the following query:

SELECT u.first_name, o.created_at FROM user AS u INNER JOIN order AS o ON o.user_id = u.id GROUP BY u.id

The data structure looks like this:

user 1

user 2

user 3

user 4

Currently the query is returning data as follow:

user 1 > order 1

user 2 > order 1

user 3 > order 1

user 4 > order 1

but I would like to have all last items of user order, as example below:

user 1 > order 3

user 2 > order 1

user 3 > order 2

user 4 > order 5

Is there a way to get this nicely? I need to retrieve last order of each user to generate the report.

Upvotes: 1

Views: 80

Answers (2)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Try this query :-

SELECT u.first_name, o.created_at
FROM user AS u
INNER JOIN order AS o ON o.user_id =  u.id
Where o.created_at=(SELECT MAX(o2.created_at)
                    FROM order o2
                    WHERE o.user_id =  o2.user_id);
GROUP BY u.id

this SELECT MAX(o2.created_at) use for get MAX created_at

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

SELECT u.first_name, max(o.created_at) as max_created_at
FROM user u
INNER JOIN order AS o ON o.user_id =  u.id
GROUP BY u.id

Upvotes: 0

Related Questions