MySQL Forums
Forum List  »  General

ORDER BY CASE datatype problem
Posted by: Torsten Brieskorn
Date: August 12, 2015 12:06PM

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?

Options: ReplyQuote


Subject
Written By
Posted
ORDER BY CASE datatype problem
August 12, 2015 12:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.