CODEWITHSUNDEEP
mysqlsqlsortingsql-order-bycase
Rj Geraci
Rj Geraci

Reputation: 185

ORDER BY CASE not behaving correctly

I have a puzzling issue with ORDER BY CASE:

I wrote a test proc with a VARCHAR(50) parameter "sortBy":

BEGIN

SELECT N.Num_Of_Users, N.Noun_Created FROM NOUNS N 
ORDER BY CASE sortBy
    WHEN 'numReviews' THEN Num_Of_Users 
     ELSE Noun_Created END DESC;
END

If I call this proc with anything but the varchar 'numReviews' it sorts by Noun_Created as expected, but if I call it with 'numReviews' it does not sort at all.

Confused by this I hard coded the sort to:

BEGIN
SELECT N.Num_Of_Users, N.Noun_Created FROM NOUNS N 
ORDER BY Num_Of_Users;
END

and it sorts correctly.

Upvotes: 0

Views: 52

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

It looks like the data types of the two columns are different - one is numeric, while the other one is a string. This may lead to some confusion when sorting.

Try this approach instead:

BEGIN

SELECT N.Num_Of_Users, N.Noun_Created FROM NOUNS N 
ORDER BY CASE sortBy  WHEN 'numReviews' END DESC,
         CASE WHEN sortBy <> 'numReviews' THEN Num_Of_Users END DESC
END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

My guess is that Num_Of_Users and Noun_Created have different types. So, the SQL engine ends up doing a type conversion (typically to a string) -- and you do not get the results you expect.

That is why it is better to use separate case statements when setting keys for the order by. Try this:

ORDER BY (CASE WHEN sortBy = 'numReviews' THEN Num_Of_Users END),
         (CASE WHEN sortBy = 'numReviews' THEN NULL ELSE Noun_Created END) DESC;

If you have only one choice, the second case is not necessary. You can just use:

ORDER BY (CASE WHEN sortBy = 'numReviews' THEN Num_Of_Users END),
         Noun_Created

Upvotes: 2

Related Questions