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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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;We can calculate the element count in the
Item Listusing thesumof the same condition as the correspondinggroup_concatas follows: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:
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.