SQL - ORDER BY sorting is incorrect

Question

I have a question, it works fine. In the last step, I want it to sort the entire table DESC based on the number of items in the "Item List" column, but it doesn't work.

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 out cold, but I'm still missing something.

item_id is stored in table2 as int(11) and then passed to table3 as a foreign key.

This is the result I get via COUNT:

This is the length I get:

Thanks!

Answer 1

If you want the result of list of items by length, the result looks like below

My list items list Empty 6,7,8,9,10,12,13,14,15,16,17,20,371 Empty 20,21,22,23,24,25 Empty 6,7,8,9,10 Empty 131 Empty 131 1,4,5 empty

Try the following query

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 LEN('Item List') DESC;

Answer 2

We can calculate the element count in the Item List using the sum of the same condition as the corresponding group_concat as follows:

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 resulting strings: for example, a list of items containing a single large number such as '10000' will still be longer than '1, 2', which contains two items.

If we want to use string functions, we can count how many commas appear in the string:

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

Side note: Two users may have the same number of items in their lists, so it might be a good idea to add another sorting criterion to break the potential tie.