Question 20196 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL – ORDER BY not ordering correctly – Mysql

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:

enter image description here

This is what I get with LENGTH:

enter image description here

Thank you!

Tags:

2

Answers


  1. 0

    If you want result as per length Item List Means like below result

    My List Item List
    NULL 6,7,8,9,10,12,13,14,15,16,17,20,371
    NULL 20,21,22,23,24,25
    NULL 6,7,8,9,10
    NULL 131
    NULL 131
    1,4,5 NULL

    TRY try below 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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search