MySQL - GROUP BY with ORDER DESC not working

Question

Hi I am having a problem with following query.

SELECT id, user_id, cloth_id FROM `items` GROUP BY user_id ORDER BY id desc LIMIT 3

I want the latest records with group by but somehow its showing old records.

I have also gone through MySQL - Group by with Order by DESC but not working as expected.

That's what's wrong with mysql. You *shouldn't be allowed* to select plain columns you're not grouping by! Add `id` to the group by and it will work. Also, _why_ are you using `Group by` if there's no aggregate functons in the select? — Alex Tartan, Jan 19 '16 at 13:34
Try this : SELECT id, user_id, cloth_id FROM `items` ORDER BY id desc GROUP BY user_id LIMIT 3 — RK12, Jan 19 '16 at 13:38
You mean you want distinct values? So why not using `SELECT DISTNICT(user_id), id, cloth_id FROM items ORDER BY id desc LIMIT 3;` ? — Liron, Jan 19 '16 at 13:40
@VickyKuwar: Your query looks okay. Please check your DB data. Is it displayed wrong? Put the whole query in phpmyadmin's SQL section and check the result data. — Ravi Hirani, Jan 19 '16 at 13:51
@Liron Distinct is not a function, it works on all columns in the SELECT clause — Niagaradad, Jan 19 '16 at 14:05

score 1 · Accepted Answer · answered Jan 19 '16 at 13:54

Try this:

SELECT i.id, i.user_id, i.cloth_id FROM
(
    SELECT max(id) as id, user_id FROM `items` GROUP BY user_id
) temp
LEFT JOIN `items` i on i.user_id = temp.user_id AND i.id = temp.id

in temp you get each user with the latest id.
in i you get the cloth_id for that combination

MySQL - GROUP BY with ORDER DESC not working

1 Answers1

Linked

Related