Good day. I'm trying to sort by most recent sale date and then group records from two related tables by customer name.
There are two tables, one called 'customers' and another 'sales'
customers
id --- name --- phone
1 --- Charles --- 300111111
2 --- Andrew --- 300111111
Sales
id --- customer_id product date
1 --- 1 --- clock --- 22-08-22
2 --- 1 --- table --- 10-08-22
3 --- 2 --- table --- 20-08-22
4 --- 2 --- pencil --- 08-15-22
I have the following Mysql query:
select clientes.*, ventas.producto from clientes
inner join ventas
on
clientes.id= ventas.id_clientes
where ventas.fecha = (select max(ventas.fecha))
order by ventas.fecha desc
I get this:
name --- product --- date
charles --- watch --- 22-08-22
andres --- table --- 20-08-22
andres --- pencil --- 08-15-22
charles --- table --- 10-08-22
It does indeed order by date, but now I need to group by name. if I do it using GROUP BY it throws me:
select clientes.*, ventas.producto from clientes
inner join ventas
on
clientes.id= ventas.id_clientes
where ventas.fecha = (select max(ventas.fecha))
group by ventas.nombre
order by ventas.fecha desc
name --- product --- date
charles --- table --- 10-08-22
andres --- pencil --- 08-15-22
It groups me but by oldest date, since the use of GROUP BY returns the first record
What I need is:
Your query is a bit complicated because it has to be solved in three steps:
a) Get the last purchase date of each customer:
b) Get the items you bought on that date:
c) And, finally, get the name of that client:
From this dataset:
You will get the result you expected:
Have you considered what would happen if Carlos had bought his two items on the same day? Let's check it with:
Getting:
Since you probably want all the products to appear on the same line, without duplicating names, you should add an extra grouping:
To get:
I hope you find it useful. Leave me any questions in the comments to improve the answer.