Sql Order By ... using `Case When` for different Ascending, Descending, and Custom Orders

Question

I want to sort product by discount on certain condition

ORDER BY 
    CASE WHEN @OrderBy = 0
    THEN table.id END ASC,
    CASE WHEN @Orderby = 2
    THEN table.id END ASC,

I want to do something like below as I don't have discount column in table

CASE WHEN @OrderBy = 4
THEN (100-((table.price/table.oldprice)*100) as discount END ASC

but it throws an error - how can I sort by discount?

Answer 1

There are quite a few problems, e.g. You can't alias a calculation field in an order by, and you'll need to escape your table name, fix the cae, and count the parenthesis.

Also, since it seems you just want to CASE on a single variable, you can move the @OrderBy out to the top of the CASE, like so:

SELECT * from [table]
ORDER BY 
    CASE @OrderBy
        WHEN  0
            THEN [table].id -- ASC
        WHEN 2
            THEN [table].id * -1 -- DESC
    ---I want to do something like below as I don't have discount column in table
        WHEN 4
            THEN (100-([table].price/[table].oldprice)*100)
    END

SqlFiddle Here

As an aside, if you need to dynamically change the ASC or DESC of a column, you can use a hack like this by multiplying by -1.

(Also note that ORDER BY CASE ... END ASC, CASE ... END ASC will set the first and then second orderings ... this doesn't seem to make sense given that @OrderBy can only have a single value)

Answer 2

It seems to me you need something similar to this

select * from TableName where someCondition >100
  order by
    case when @OrderBy = 'AirlineService'
      then AirlineService END desc,
    case when  @OrderBy = 'SomeMore'
      then  MyOtherColumn  end
GO

If you not have a coulmn then you can not sort with that. Please read this Microsoft Link Please keep in mind - specifies the sort order used on columns returned in a SELECT statement. Hope it helps.