How to get latest record in GROUP BY in SQL-CodePudding

I write this query:

SELECT * 
FROM `peer_settings` 
WHERE `sorting_hub_id` LIKE '[\"172\"]' 
GROUP BY product_id 
ORDER BY `peer_settings`.`product_id` ASC

when I run this query I got Id 488 product id 100

but I want id 46595 product id 100

enter image description here

CodePudding user response：

You can try using the having clause

SELECT * 
FROM `peer_settings` 
WHERE `sorting_hub_id` LIKE '[\"172\"]' 
GROUP BY product_id  having max(id)
ORDER BY `peer_settings`.`product_id` ASC

CodePudding user response：

in MySQL, you can order by more than one column:

SELECT * 
FROM `peer_settings` 
WHERE `sorting_hub_id` LIKE '[\"172\"]' 
GROUP BY product_id 
ORDER BY `peer_settings`.`product_id` ASC,`peer_settings`.`id` DESC
LIMIT 1