GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

using case in order by clause causes sort by character instead of integer

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • using case in order by clause causes sort by character instead of integer

    We recently updated some code in which we use a "case" statement within an order clause, as follows:

    order by
    case d.wo
    when 'e' then a.location
    when 'a' then c.w
    when 'r' then RAND()
    end asc";

    where a.location is a tinyint.

    Sorting directly by a.location works fine, values are sorted correctly, i.e. 1..9,10,11... etc.

    Using the case statement as above, treats "a.location" as a character and causes the sort order to change, i.e. 1,10,11,2,3..9 etc.

    I tried forcing a cast in the case statement on a.location but that did not work. Any suggestions? is this a bug?

  • #2
    What is the type of c.w?

    Comment


    • #3
      varchar

      Comment


      • #4
        That will need to be cast to a number, or the whole case will be considered as a varchar. The expression is evaluated as a type that all of its parts can be cast implicitly to, and varchar can't be cast to int implicitly.

        Comment

        Working...
        X