SQL - ORDER BY not ordering correctly

Question

I have a query, works fine. In the last step I want it to order the whole table DESC based on the number of items in the 'Item List' column, but it's not working.

SELECT t8.username AS 'Username',
       GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `My Item List`,
       GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `Item List`
FROM table1 t1
LEFT JOIN table3 t2 USING (item_id)
JOIN table2 t5 ON t5.id = t2.user_id
JOIN accounts t8 ON t8.id = t2.user_id
WHERE t1.user_id = 23
  AND t2.user_id <> 23
  GROUP BY t2.user_id
  HAVING `Item List` is not null or `My Item List` is not null
  ORDER BY COUNT('Item List') DESC;

I'm pretty sure I'm colse, but I'm still missing something.

The item_id is stored in table2 as int(11) then gave it to table3 as Foreign Key.

This is what I get with COUNT:

This is what I get with LENGTH:

Thank you!

Answer 1

We can compute the count of elements in Item List with a sum that uses the same conditions as the corresponding group_concat, like so:

SELECT t8.username AS 'Username',
    GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `My Item List`,
    GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 AND t5.item_name='lamp' THEN item_id END ORDER BY item_id SEPARATOR ', ') `Item List`
FROM table1 t1
...
ORDER BY SUM(CASE WHEN t2.dup=1 AND t1.stat=0 AND t5.item_name='lamp' THEN 1 ELSE 0 END) DESC

This is safer than just comparing the length of the generated string: for example, an item list that contains a single big number (like '10000') is still longer than '1, 2', which contains two items.

If we were to use string functions, we could count how many commas appear in the string instead:

ORDER BY LENGTH(`Item List`) - LENGTH(REPLACE(`Item List`, ', ', '')) DESC

Side note: two users might have the same number of items in their list, so it would probably a good idea to add another sort criteria to break potential ties.