0

i have a query like this :

select DATE_FORMAT(o.created_at, '%Y-%m') as date,
JSON_EXTRACT(oi.inventory, '$.id') as inv_id,
count(JSON_EXTRACT(oi.inventory, '$.id')) as inv_count
from orders as o inner join order_items as oi on oi.order_id = o.id
where o.created_at >= (CURDATE() + INTERVAL (1 - DAY(CURDATE())) DAY) - INTERVAL 12 MONTH AND
o.created_at < (CURDATE() + INTERVAL (1 - DAY(CURDATE())) DAY) + INTERVAL 1 MONTH 
group by date, inv_id 
order by date desc limit 10

and the result is :

date inv_id inv_count
2023-01 1 22
2023-01 2 29
2022-12 1 1
2022-12 2 2
2022-11 1 2
2022-11 2 1
2022-10 1 2
2022-10 2 1

but all i want is result like this :

date inv_id inv_count
2023-01 2 29
2022-12 2 2
2022-11 1 2
2022-10 1 2

how to grouping data like that? because i can't use max(inv_count) inside group by

MatBailie
  • 83,401
  • 18
  • 103
  • 137
Evan Mahendra
  • 1
  • 2
  • why you don't ``select data,inv_id,max(inv_count) from (yourquery) a group by data,inv_id`` – mikasa Jan 24 '23 at 08:56
  • Which version of MySQL? It matters because `MySQL 8` has functionality not present in `MySQL 5.x`. Also, will there only ever be TWO inv_id values? – MatBailie Jan 24 '23 at 09:05
  • i'm using mysql 5.7.26 – Evan Mahendra Jan 24 '23 at 09:08
  • it can be more than two, but the point is, i want to get highest count value per month and the inv_id – Evan Mahendra Jan 24 '23 at 09:14
  • @EvanMahendra Then there are no 'clean' options. I recommend storing the full results in a temporary table and then using one of the many `greatest-n-per-group` answers from SO, for example; https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql/1313293#1313293 – MatBailie Jan 24 '23 at 09:24
  • @MatBailie okay i'll try to store the result in a temporary table first, thank you Mat – Evan Mahendra Jan 24 '23 at 09:24

0 Answers0