MySQL Using ORDER BY and GROUP BY together

Question

I have a table with products that have id price category etc... I am trying to extract 1 item from each item_category, which is the most recent one (has the highest ID amongst its own category).

Here is the code, it gets me 6 items from 6 categories but they are the oldest (with the smallest ID)

    SELECT * from items WHERE item_category = '2' or item_category = '4' or 
item_category = '12' or item_category = '13' or item_category = '14' or 
item_category = '19' GROUP BY `item_category`  ORDER BY `item_id`  LIMIT 6

Order by ID is executed on the returned array of 6 items, and no on the original table.

How can I exctract the newest items instead?

the best query i think

select *
from items
where item_category in ("2","4","12","13","14","19")
group by item_category
order by item_category DESC

your query can be like this `SELECT * FROM items WHERE item_category IN('2', '4', '12', '13', '14' ,'19') ORDER BY item_id DESC LIMIT 6` where `ORDER BY item_id DESC` expression that sort rows by `item_id` in descendant order — Artem Ilchenko, Oct 31 '17 at 08:58
If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause — P.Salmon, Oct 31 '17 at 08:59
could just `SELECT item_category, MAX(item_Id) ... GROUP BY item_category`. However I'm surprised that MySQL isn't throwing a fit when you're trying to group by a single column of what you selected instead of being forced to group by all non-aggregate columns. You're not supposed to do that — apokryfos, Oct 31 '17 at 09:17
@apokryfos yes MAX ITEM ID returns them properly, so after that all I need to do is use the returned array to select the actual items, correct? I'm not very familiar with the functions and other complex uses... — Denislav Karagiozov, Oct 31 '17 at 09:20
@DenislavKaragiozov, the answer from @apokryfos is correct and should be written up as an answer. I'd write it up but want him to get the score. You can add the * to the SELECT so that you get the grouping and sorting and also the full item details: `SELECT item_category, MAX(item_id) AS max_id, * FROM items WHERE item_category IN (...) GROUP BY item_category ORDER BY max_id` — davidethell, Oct 31 '17 at 09:23
Well you could also do `SELECT * FROM items WHERE id IN (SELECT MAX(item_id) /* your original query */)` — apokryfos, Oct 31 '17 at 09:23
@davidethell @apokryfos `SELECT item_category, MAX(item_id) AS max_id, * FROM items WHERE item_category IN ("2","4","12","13","14","19") GROUP BY item_category ORDER BY max_id` throws an error though... and im not sure what part of the original query i need to put in the /* your original query*/ from apokryfos answer — Denislav Karagiozov, Oct 31 '17 at 09:30

apokryfos · Accepted Answer · 2017-10-31T10:11:41.407

5

First of all just to point out, you should train yourself to not use group by if you are not intending to include all non-aggregated columns in the grouping. Example:

SELECT col1,col2 FROM table GROUP BY col1 -- BAD
SELECT col1,col2 FROM table GROUP BY col1,col2 -- GOOD
SELECT col1,MAX(col2) FROM table GROUP BY col1 -- GOOD

This is because of the obvious problem that you get an arbitrary and unpredictable value in columns that are not unique within the group which is the source of all your problems.

Having said that you should do this:

SELECT item_category, MAX(item_id) 
FROM items 
WHERE item_category IN ('2','4','12','13','14','19') GROUP BY `item_category`

Note that there is no need to limit since you can only have 6 groups and order by doesn't do anything anymore.

If you want to get the corresponding item rows you can do the following:

SELECT * 
FROM items 
WHERE item_id IN ( 
     SELECT MAX(item_id) 
     FROM items 
     WHERE item_category IN ('2','4','12','13','14','19') 
     GROUP BY item_category
);

The following might also work:

SELECT * 
FROM items i JOIN (
       SELECT i2.item_category, MAX(i2.item_id) as max_item_id 
       FROM items i2
       WHERE i2.item_category IN ('2','4','12','13','14','19') GROUP BY 
       i2.`item_category`
) max_ids ON i.item_id=max_ids.max_item_id;

edited Oct 31 '17 at 10:11

answered Oct 31 '17 at 09:32

apokryfos

38,771
9
70
114

Thanks for the code and your time !! What was the ultimate final solution to me was adding * to the first SELECT and that returned every column for every item. SELECT *, item_category, MAX(item_id) FROM items WHERE item_category IN ('2','4','12','13','14','19') GROUP BY `item_category` – Denislav Karagiozov Oct 31 '17 at 09:38
You need to be very careful when doing that. You can't necessarily be sure that the columns returned as part of the `*` correspond with the `MAX(item_id)` – apokryfos Oct 31 '17 at 09:41
Yeah, they weren't.... so the actual final query is - SELECT * from items WHERE item_id IN ( SELECT MAX(item_id) FROM items WHERE item_category IN ('2','4','12','13','14','19') GROUP BY `item_category` ) – Denislav Karagiozov Oct 31 '17 at 09:42
Updated the answer to also reflect what you actually did. Not sure if the join is better than the select/in. I suspect they'd be more or less the same performance-wise. – apokryfos Oct 31 '17 at 10:12
the action is pretty simple, IMO the difference would be negligible. Well done nevertheless! – Denislav Karagiozov Oct 31 '17 at 11:36

MySQL Using ORDER BY and GROUP BY together

1 Answers1