ORDER BY on INT column inside CASE expression not working properly

Question

I have a table that looks like

some_table (
    item_id varchar(10) null,
    item_title varchar(200) null,
    item_url varchar(200) null,
    item_status char default '0' null,
    access_count int null,
    created_by varchar(100) default 'system' not null,
    created_date timestamp default CURRENT_TIMESTAMP not null
)

When I try to execute the following SQL, the result order turns out to be incorrect, for example, 91 comes after 912.

SET @orderCol = 'accessCount';
SELECT
    t.item_status,
    t.item_id,
    t.item_title,
    t.item_url,
    t.access_count
FROM some_table t
WHERE DATE(t.created_date) = DATE(NOW())
ORDER BY CASE
    WHEN (@orderCol = 'itemStatus') THEN t.item_status
    WHEN (@orderCol = 'itemId') THEN t.item_id
    WHEN (@orderCol = 'itemTitle') THEN t.item_title
    WHEN (@orderCol = 'itemUrl') THEN t.item_url
    WHEN (@orderCol = 'accessCount') THEN t.access_count 
END DESC
LIMIT 0, 30

What should I do to fix this problem?

Answer 1

You are mixing data types inside the CASE expression and it says here that:

The return type of a CASE expression result is the aggregated type of all result values.

In your example the return type would be VARCHAR(200); the largest data type capable of storing all possible values. This explains why your numbers are sorted as strings. A better solution is to sort the columns separately; combine only if they are compatible:

ORDER BY
    -- string columns
    CASE
        WHEN (@orderCol = 'itemStatus') THEN t.item_status
        WHEN (@orderCol = 'itemId') THEN t.item_id 
        WHEN (@orderCol = 'itemTitle') THEN t.item_title 
        WHEN (@orderCol = 'itemUrl') THEN t.item_url 
    END,
    -- numeric columns
    CASE
        WHEN (@orderCol = 'accessCount') THEN t.access_count
    END

The first faux column will consist entirely of NULL values when none of the WHEN clauses match; in which case (of ties) the second faux column is used.