locked
concatenate int with varchar in order by RRS feed

  • Question

  • User-1826049516 posted

    Hi,

    I want to do this:

    order by	case
    			when @Grouping = 0 then coalesce(s.Rank, d.Rank)
    			else ''
    		end + e.EMP_Surname
    

    However SQL rightly moans about converting varchar to int. Rank is int, EMP_Surname is varchar. I can't cast Rank as varchar because I'll lose correct ordering - it would go 1, 10, 2, 3, 4, etc.

    Thursday, March 1, 2018 1:29 PM

Answers

  • User452040443 posted

    Hi,

    Try something like this:

    order by
        case when @Grouping = 0 
            then coalesce(s.Rank, d.Rank)
            else 0
        end,
        e.EMP_Surname

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 1, 2018 1:40 PM
  • User753101303 posted

    Hi,

    I would order on two separate expressions ie :

    order by case when @Grouping=0 then coalesce(s.rank,d.rank) else 0 end, e.EMP_surname

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 1, 2018 1:52 PM
  • User-1716253493 posted
    order by case when @Grouping = 0 then s.Rank else 0 end,
    d.Rank, e.EMP_Surname

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 2, 2018 12:24 AM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    order by
        case when @Grouping = 0 
            then coalesce(s.Rank, d.Rank)
            else 0
        end,
        e.EMP_Surname

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 1, 2018 1:40 PM
  • User753101303 posted

    Hi,

    I would order on two separate expressions ie :

    order by case when @Grouping=0 then coalesce(s.rank,d.rank) else 0 end, e.EMP_surname

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 1, 2018 1:52 PM
  • User-1716253493 posted
    order by case when @Grouping = 0 then s.Rank else 0 end,
    d.Rank, e.EMP_Surname

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 2, 2018 12:24 AM