Select with max(date), penultimate(date)

Asked

Viewed 1,026 times

0

I have a chart with product code and date of sale. I would like to make a select by taking the product code, the date of the last sale and the date of the penultimate sale with group by code. It is 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;
mysql select-sql mysql-workbench order-by
  • 4

    You need to give more information about your query, post the code you were working on so it is possible to guide you to the correct answer.

  • @Marcoviniciussoaresdalalba: select p.codigo, max(date_format(p.data_sale, '%d/%m/%Y')) Data_ultima_sale, (max(date_format(p.data_sale, '%d/%m/%Y'))-interval 1 day) Data_penultima_sale from items i Inner Join requests p on p.id=i.pedido_id group by p.codigo order by p.data_sale DESC limit 10;

  • Edith your question and add code.

1 answer

1


I couldn’t test 100% here but I believe this is what you’re looking for...

What I did, I made a select like yours, but I took the max data_venda which is not the same as the first obviously being the last

 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;
  • 1

    It worked perfectly, thank you!

Browser other questions tagged mysql select-sql mysql-workbench order-by

You are not signed in. Login or sign up in order to post.