I have a table with product code and date of sale. I would like to make a select picking the product code, the date of the last sale and the date of the next-to-last sale with group by code. Is it possible?
select
p.codigo,
max(date_format(p.data_venda, '%d/%m/%Y')) Data_Ultima_Venda,
(max(date_format(p.data_venda, '%d/%m/%Y'))-interval 1 day) Data_Penultima_Venda
from
itens i
inner join pedidos p on p.id=i.pedido_id
group by p.codigo
order by p.data_venda DESC
limit 10;
I could not test 100% here but I believe this is what you're looking for ...
What I did, I made a select equal to yours, but I got the max data_venda that is not equal to the first obviously being the penultimate
select
p.id,
max(p.data_venda) Data_Ultima_Venda,
(select
max(p2.data_venda)
from pedidos p2
where p2.id= p.id
and p2.data_venda <> max(p.data_venda)
group by p2.id
) Data_Penultima_Venda
from itens i
inner join pedidos p on p.id=i.pedido_id
group by p.id
order by p.data_venda DESC
limit 10;