CODEWITHSUNDEEP
mysqlsql
Dhananjay Sakhare
Dhananjay Sakhare

Reputation: 494

MySql sorting working abnormally

I have written a stored procedure which returns sorted(ascending or descending) data based on the column which user has selected. To achieve that I used Case statements in my order by clause like following code snippet

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END,
    CASE    WHEN p_filter_type = 'DESC' THEN
                CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END DESC

Issue in this code is that it does not sort data properly if any column with integer data type is selected. It considers integer value as varchar and sorts the data accordingly

For e.g. It returns 

10
1
2
3

If requestId is selected in ASCENDING order.

I tried using couple of solutions for the problem like use ABS() to convert varchar value to integer etc but they are not helping me solve the issue.

I would appreciate if you guys help me to solve the issue

Upvotes: 2

Views: 96

Answers (6)

trincot
trincot

Reputation: 350280

Indeed, when you combine different data types like that, the expression gets converted to varchar, even when p_filter_type and p_filter_column indicate that only a numeric column should determine the outcome of that expression.

One way to solve this, is to create a separate order by expression for each of the possibilities, producing null for all of them except for the one that is relevant. That way each of the expressions can stick to its own data type:

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'projectCode' 
        THEN projectcode END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'visaType' 
        THEN visaType END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'approveRejectStatus' 
        THEN status END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'createdDate'
        THEN createdDate END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'employeeID'
        THEN employeeID END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'requestId'
        THEN requestId END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'country'
        THEN country END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'serviceDesk'
        THEN serviceDesk END ASC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'projectCode' 
        THEN projectcode END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'visaType' 
        THEN visaType END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'approveRejectStatus' 
        THEN status END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'createdDate'
        THEN createdDate END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'employeeID'
        THEN employeeID END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'requestId'
        THEN requestId END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'country'
        THEN country END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'serviceDesk'
        THEN serviceDesk END DESC

For instance, when p_filter_type = 'DESC' and p_filter_column = 'employeeID' then the above ORDER BY clause really resolves to the following:

ORDER BY 
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL DESC,
    NULL DESC,
    NULL DESC,
    NULL DESC,
    employeeID DESC,
    NULL DESC,
    NULL DESC,
    NULL DESC

which should order the same way as if you had just written:

ORDER BY 
    employeeID DESC

Hybrid solution

You could of course go for some in-between solution, where you group the fields that have the same data type together in one expression, so that no data type conversion will take place. That way you will have maybe 6 expressions in your order by clause: one for varchar fields, one for int fields, one for date fields, and then each of those repeated for the descending case:

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column -- all varchar fields
            WHEN 'projectCode' THEN projectCode
            WHEN 'visaType' THEN visaType
            WHEN 'approveRejectStatus' THEN status
            WHEN 'country' THEN country
            WHEN 'serviceDesk' THEN serviceDesk
        END
    END ASC,
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column -- all int fields 
            WHEN 'employeeID' THEN employeeId
            WHEN 'requestId' THEN requestId
        END
    END ASC,
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column -- all date fields
            WHEN 'createdDate' THEN createdDate
        END
    END ASC,
    CASE WHEN p_filter_type = 'DESC' THEN
        CASE p_filter_column -- all varchar fields
            WHEN 'projectCode' THEN projectCode
            WHEN 'visaType' THEN visaType
            WHEN 'approveRejectStatus' THEN status
            WHEN 'country' THEN country
            WHEN 'serviceDesk' THEN serviceDesk
        END
    END DESC,
    CASE WHEN p_filter_type = 'DESC' THEN
        CASE p_filter_column -- all int fields 
            WHEN 'employeeID' THEN employeeId
            WHEN 'requestId' THEN requestId
        END
    END DESC,
    CASE WHEN p_filter_type = 'DESC' THEN
        CASE p_filter_column -- all date fields
            WHEN 'createdDate' THEN createdDate
        END
    END DESC

Upvotes: 2

Paul Spiegel
Paul Spiegel

Reputation: 31802

You can generate a prepared statement using the CONCAT function:

SET @sql = CONCAT('select * from some_table order by ', p_filter_column, ' ', p_filter_type);
PREPARE stmt FROM @sql;
EXECUTE stmt;

As a bonus, the prepared statement can use indexes (if defined).

However you might need to validate the input first.

Another workaround could be to use LPAD for numeric fields and fill the number with zeros:

WHEN 'employeeID' THEN LPAD(employeeId, 20, '0') -- int field

But it will only work for UNSIGNED types.

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

Since CASE returns an atomic value, the returned datatype can only be single. You can't have a different returned datatype on a different execution. In this case, everything gets implicitly converted to a varchar. In order to get the order you want, you have to make sure the integers will order according to string ordering rules. Like so:

CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN RIGHT(CONCAT('00', employeeId), 2) -- int field
            WHEN 'requestId' THEN RIGHT(CONCAT('00', requestId), 2) -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field
        END
END

For the ordering, it will alter the values to 01, 02, 03, 10, which will sort the way you want them to.

I'm not sure if your dates will be ordered properly though, that depends on your local settings.

Upvotes: 0

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN CAST(projectCode AS INT) -- varchar field
            WHEN 'visaType' THEN CAST(visaType AS INT) -- varchar field
            WHEN 'approveRejectStatus' THEN CAST(status AS INT) -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN CAST(country AS INT) -- varchar field
            WHEN 'serviceDesk' THEN CAST(serviceDesk AS INT) -- varchar field

    END
    END,
    CASE    WHEN p_filter_type = 'DESC' THEN
                CASE p_filter_column 
           WHEN 'projectCode' THEN CAST(projectCode AS INT) -- varchar field
            WHEN 'visaType' THEN CAST(visaType AS INT) -- varchar field
            WHEN 'approveRejectStatus' THEN CAST(status AS INT) -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN CAST(country AS INT) -- varchar field
            WHEN 'serviceDesk' THEN CAST(serviceDesk AS INT) -- varchar field

    END
    END DESC

I think as your column with varchar type contains digits and you want to sort this field according to number, you need to cast that VARCHAR field to INT during ordering as i had tried in above code. Hope this will help you.

Upvotes: 0

Blank
Blank

Reputation: 12378

If you just want to convert varchar value to integer, assuming these values are digits, mysql can do it simply, let that column plus 0, like this:

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId + 0 -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END,
    CASE    WHEN p_filter_type = 'DESC' THEN
                CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId + 0 -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END DESC

see a simple demo in sqlfiddle.

Upvotes: 0

Whencesoever
Whencesoever

Reputation: 2296

How about adding that ASC order to first case ? 

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END ASC,
    CASE    WHEN p_filter_type = 'DESC' THEN
                CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END DESC

does it work? Also, are You sure that date You used as example of wrong sorting is not in Varchar column? It looks like it works as one.

Upvotes: 0

Related Questions