ORDER BY CASE datatype problem
in my stored procedure the ORDER BY clause sorted by String and i will order by right current datatype (INTEGER).
Here the stored procedure they sorted false:
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_test_order_by`(order_by VARCHAR(20))
BEGIN
select
t.id as topic_id, t.title as topic_title, t.alias as topic_alias, t.time_create, t.is_public,
(SELECT COUNT(ec.id) FROM entry_contra ec WHERE ec.topic_id = t.id) as count_ec,
(SELECT COUNT(ep.id) FROM entry_pro ep WHERE ep.topic_id = t.id) as count_ep,
(SELECT IFNULL(count_ec,0) + IFNULL(count_ep,0)) as count_eall,
(SELECT COUNT(wc.id) FROM weighting_contra wc WHERE wc.entry_contra_id in (SELECT ec.id FROM entry_contra ec WHERE ec.topic_id = t.id)) as count_wc,
(SELECT COUNT(wp.id) FROM weighting_pro wp WHERE wp.entry_pro_id in (SELECT ep.id FROM entry_pro ep WHERE ep.topic_id = t.id)) as count_wp,
(SELECT IFNULL(count_wc,0) + IFNULL(count_wp,0)) as count_wall
from topic t
ORDER BY
CASE
WHEN order_by = 'topic_alias' THEN topic_alias
WHEN order_by = 'time_create' THEN time_create
WHEN order_by = 'count_ec' THEN count_eall
WHEN order_by = 'count_ep' THEN count_eall
WHEN order_by = 'count_wc' THEN count_wall
WHEN order_by = 'count_wp' THEN count_wall
ELSE time_create
END
DESC;
END
Without the CASE in ORDER BY it sorted correct:
select
t.id, t.title,
(SELECT COUNT(ec.id) FROM entry_contra ec WHERE ec.topic_id = t.id) as count_ec,
(SELECT COUNT(ep.id) FROM entry_pro ep WHERE ep.topic_id = t.id) as count_ep,
(SELECT IFNULL(count_ec,0) + IFNULL(count_ep,0)) as count_eall,
(SELECT COUNT(wc.id) FROM weighting_contra wc WHERE wc.entry_contra_id in (SELECT ec.id FROM entry_contra ec WHERE ec.topic_id = t.id)) as count_wc,
(SELECT COUNT(wp.id) FROM weighting_pro wp WHERE wp.entry_pro_id in (SELECT ep.id FROM entry_pro ep WHERE ep.topic_id = t.id)) as count_wp,
(SELECT IFNULL(count_wc,0) + IFNULL(count_wp,0)) as count_wall
from topic t
WHERE t.is_public = 1
GROUP BY t.id
ORDER BY count_wall
DESC;
Why MySQL sorted not correct with CASE in ORDER BY?
Can someone help me?