Group by with Order Desc in MySQL
i recently worked on auto complete records task. They expect to show auto complete results must be unique and recent records. i use following simple query with my filter.
For example,
we have the following table – items
id | name | price
1 | a | 20
2 | b | 25
3 | c | 20
4 | a | 30
5 | c | 21
Here we need latest items group by name. i use the following query.
SELECT *
FROM `items`
GROUP BY name
ORDER BY id DESC
id | name | price
3 | c | 20
2 | b | 25
1 | a | 20
Normally MySQL ordered as ascending by primary key while grouping. So we should order before grouping.
SELECT *
FROM (
SELECT *
FROM `items`
ORDER BY id DESC
) AS `items`
GROUP BY `items`.name
id | name | price
4 | a | 30
2 | b | 25
5 | c | 21
Latest posts by Kapil Dev (see all)
- Travelling Salesman Problem using Branch and Bound Approach in PHP - July 5, 2016
- Javascript: Print content of particular div - March 4, 2015
- Group by with Order Desc in MySQL - June 3, 2014