Dhananjay Sakhare Dhananjay Sakhare -4 years ago 65
SQL Question

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

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download