Change order of the result

[kult]

Hello,

I generate a request in php like this:
SELECT COUNT(*), MGSROO_ID FROM T_REPLY_RPY WHERE MGSROO_ID = 7 OR MGSROO_ID = 16 OR MGSROO_ID = 8 OR MGSROO_ID = 11 OR MGSROO_ID = 14 OR MGSROO_ID = 1 OR MGSROO_ID = 2 OR MGSROO_ID = 4 GROUP BY MGSROO_ID

There can't be more than 20 MGSROO_ID = X ... But the order of the result does not correspond to what I wanted, It's ordered by MGSROO_ID number:

 +----------+-----------+
| COUNT(*) | MGSROO_ID |
+----------+-----------+
|        1 |         1 |
|       18 |         2 |
|        8 |         4 |
|        5 |         7 |
|       33 |         8 |
|       19 |        11 |
+----------+-----------+ 

And yet i would like the result like this (MGSROO_ID):
7, 16, 8, 11, 14, 1, 2, 4

Is there any way to change the order of the result to get the same order of the "OR" requests?

Thanks.

[the_Igel]

I think not.
But you can read your result in an array and then sort the array in any order you want usung for example usort().

And you'd better generate query like

SELECT smth FROM somewhere WHERE mgsroo_id IN (1,2, 5, 7, 12, 76) .......

it helps to keep query shorter and easier.

[tomhath]

It can be done with a "CASE" but it's kind of verbose:

SELECT smth FROM somewhere WHERE mgsroo_id IN (1,2, 5, 7, 12, 76)
order by case
when mgsroo_id = 7 then 0
when mgsroo_id = 16 then 1
when mgsroo_id = 8 then 2
else 9999
end

[kult]

OK, thanks to all, now my generated request looks like this:
SELECT COUNT(*), MGSROO_ID
FROM T_REPLY_RPY WHERE MGSROO_ID IN (7,16,8,11,14,1,2,4) GROUP BY MGSROO_ID
ORDER BY CASE
WHEN MGSROO_ID = 7 THEN 0
WHEN MGSROO_ID = 16 THEN 1
WHEN MGSROO_ID = 8 THEN 2
WHEN MGSROO_ID = 11 THEN 3
WHEN MGSROO_ID = 14 THEN 4
WHEN MGSROO_ID = 1 THEN 5
WHEN MGSROO_ID = 2 THEN 6
WHEN MGSROO_ID = 4 THEN 7
END

And the request result is like this: 😃

+----------+-----------+
| COUNT(*) | MGSROO_ID |
+----------+-----------+
|        5 |         7 |
|       33 |         8 |
|       19 |        11 |
|        1 |         1 |
|       18 |         2 |
|        8 |         4 |
+----------+-----------+

But I noticed somthing odd, there is no NULL result when the COUNT(*) is equal to 0 instead of displaying 0, it does not display anything!

Is that normal, it might be for MySQL, but not for me :rolleyes:

Thanks,
Vincent.

[tomhath]

Try changing to this:

SELECT COUNT(MGSROO_ID), MGSROO_ID
...

[kult]

Hello,

This query:
SELECT COUNT(MGSROO_ID), MGSROO_ID
FROM T_REPLY_RPY WHERE MGSROO_ID IN (7,16,8,11,14,1,2,4) GROUP BY MGSROO_ID
ORDER BY CASE
WHEN MGSROO_ID = 7 THEN 0
WHEN MGSROO_ID = 16 THEN 1
WHEN MGSROO_ID = 8 THEN 2
WHEN MGSROO_ID = 11 THEN 3
WHEN MGSROO_ID = 14 THEN 4
WHEN MGSROO_ID = 1 THEN 5
WHEN MGSROO_ID = 2 THEN 6
WHEN MGSROO_ID = 4 THEN 7
END

And it gives me the same result:

+------------------+-----------+
| COUNT(MGSROO_ID) | MGSROO_ID |
+------------------+-----------+
|                6 |         7 |
|               35 |         8 |
|               18 |         2 |
|               19 |        11 |
|                1 |         1 |
|                8 |         4 |
+------------------+-----------+

no matter, i put a condition (if) to avoid the trouble:

if($count_comments->f("MGSROO_ID") == $messages_room->f("MGSROO_ID")) {
	echo $count_comments->f("COUNT(*)") ;
	$count_comments->next_record() ;
}else {echo "0";}

Ok thanks to all guys
🆒

Vincent.