CODEWITHSUNDEEP
sqlmariadb
Alex1844
Alex1844

Reputation: 333

Why is my MariaDB SQL query sorting INT as VARCHAR?

I have a rather complex query that works fine, except that when it orders results by the INT column, it orders them as if it is VARCHAR

I have even tried casting the column (id) to INT in ORDER BY, even though the column is INT by itself, but it still orders wrong.

SELECT 
`employees`.`id` AS id,
`employees`.`FirstName` AS `FirstName`,
`employees`.`LastName` AS `LastName`,
`employees`.`Email` AS `Email`,
`employees`.`UserName` AS `UserName`,
`employees`.`BadgeNumber` AS `BadgeNumber`,
`employees`.`EmpEmergencyNum` AS `EmpEmergencyNum`,
`departments`.`DeptName` AS `DeptName`,
`emptypes`.`EmpType` AS `EmpType`,
`jobtitles`.`JobTitle` AS `JobTitle`,
`listcarriers`.`CarrierName` AS `CarrierName`,
`paprofiles`.`PAProfile` AS `PAProfile`,
`empstatuses`.`Status` AS `Status`,
concat(`employees`.`LastName`,', ',`employees`.`FirstName`) AS `EmpName`
FROM `employees`

LEFT JOIN `departments` ON
    (`employees`.`DeptID` = `departments`.`id`)
LEFT JOIN `emptypes` ON
    (`employees`.`EmpTypeID` = `emptypes`.`id`)
LEFT JOIN `jobtitles` ON
    (`employees`.`JobTitleID` = `jobtitles`.`id`)
LEFT JOIN `listcarriers` ON
    (`employees`.`EmpCarrierID` = `listcarriers`.`id`)
LEFT JOIN `empstatuses` ON
    (`employees`.`EmpStatusID` = `empstatuses`.`id`)
LEFT JOIN `paprofiles` ON
    (`employees`.`PAProfileID` = `paprofiles`.`id`)

WHERE ((`employees`.`FirstName` LIKE CONCAT('%', _empname, '%') OR `employees`.`LastName` LIKE CONCAT('%', _empname, '%'))
    AND `email` LIKE CONCAT('%', _email, '%')
    AND `username` LIKE CONCAT('%', _username, '%')
    AND `badgenumber` LIKE CONCAT('%', _badgenumber, '%')
    AND `employees`.`DeptID` = IF(_DeptID = 0, `employees`.`DeptID`, _DeptID ) 
    AND `employees`.`EmpTypeID` = IF(_EmpTypeID = 0, `employees`.`EmpTypeID`, _EmpTypeID)
    AND `employees`.`JobTitleID` = IF(_JobTitleID = 0, `employees`.`JobTitleID`, _JobTitleID)
    AND `employees`.`EmpStatusID` = IF(_EmpStatusID = 0, `employees`.`EmpStatusID`, _EmpStatusID)
    AND `employees`.`PAProfileID` = IF(_PAProfileID = 0, `employees`.`PAProfileID`, _PAProfileID))

ORDER BY 
        CASE
        WHEN _order='DESC' THEN CASE
        WHEN _sort='id' THEN CAST(`employees`.id AS int)
            WHEN _sort='LastName' THEN LastName
            WHEN _sort='FirstName' THEN FirstName
            WHEN _sort='Email' THEN Email
            WHEN _sort='UserName' THEN UserName
            WHEN _sort='BadgeNumber' THEN BadgeNumber
            WHEN _sort='EmpEmergencyNum' THEN EmpEmergencyNum
            WHEN _sort='DeptName' THEN DeptName
            WHEN _sort='EmpType' THEN EmpType
            WHEN _sort='JobTitle' THEN JobTitle
            WHEN _sort='CarrierName' THEN CarrierName
            WHEN _sort='PAProfile' THEN PAProfile
            WHEN _sort='Status' THEN `Status`
            END
        END DESC,
        CASE
        WHEN _order='ASC' THEN CASE
        WHEN _sort='id' THEN CAST(`employees`.id AS int)
            WHEN _sort='LastName' THEN LastName
            WHEN _sort='FirstName' THEN FirstName
            WHEN _sort='Email' THEN Email
            WHEN _sort='UserName' THEN UserName
            WHEN _sort='BadgeNumber' THEN BadgeNumber
            WHEN _sort='EmpEmergencyNum' THEN EmpEmergencyNum
            WHEN _sort='DeptName' THEN DeptName
            WHEN _sort='EmpType' THEN EmpType
            WHEN _sort='JobTitle' THEN JobTitle
            WHEN _sort='CarrierName' THEN CarrierName
            WHEN _sort='PAProfile' THEN PAProfile
            WHEN _sort='Status' THEN `Status`
            END
        END ASC
LIMIT _start, _count;
END

Expected 11, 12, 13, 20, 21, 101, 102, 201 Actual 101, 102, 11, 12, 13, 20, 201, 21

I am really puzzled by this

Upvotes: 1

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Don't use a single case expression in this case. A case expression returns a single value with a single type. Instead, use separate case expressions:

ORDER BY (CASE WHEN _order = 'DESC' AND _sort = 'id' THEN employees.id END) DESC,
         (CASE WHEN _order = 'DESC' AND _sort = 'LastName' THEN LastName END) DESC,
         . . .,
         (CASE WHEN _order = 'ASC' AND _sort = 'id' THEN employees.id END) ASC,
         (CASE WHEN _order = 'ASC' AND _sort = 'LastName' THEN LastName END) ASC,
         . . .,

Upvotes: 1

Related Questions