5

How to use order by on a calculated field in SQL?

select a.Customer
    ,a.PlanTo
    ,a.Dollar01
    ,a.Dollar02
    ,a.Dollar03
    ,a.Dollar04
    ,a.Dollar05
    ,a.Dollar06
    ,a.Dollar07
    ,a.Dollar08
    ,a.Dollar09
    ,a.Dollar10
    ,a.Dollar11
    ,a.Dollar12
    ,(CAST(a.Dollar01 as decimal) + CAST(a.Dollar02 as decimal) 
    + CAST(a.Dollar03 as decimal) + CAST(a.Dollar04 as decimal) 
    + CAST(a.Dollar05 as decimal) + CAST(a.Dollar06 as decimal) 
    + CAST(a.Dollar07 as decimal) + CAST(a.Dollar08 as decimal) 
    + CAST(a.Dollar09 as decimal) + CAST(a.Dollar10 as decimal) 
    + CAST(a.Dollar11 as decimal) + CAST(a.Dollar12 as decimal)) as TOTAL1
from MDM_STAT.sds.SMarginText a
where a.salesyear = '2016'
order by a.total1

This is giving me 'Total1' column does not exist but as you see I have created it and is working if I am not using the order by clause.

RB17
  • 356
  • 1
  • 8
  • 26

3 Answers3

15

You can do what Mureinik suggests and use the ordinal notation of ORDER BY 13, which means "order by the 13th column." However, I would tend to avoid it because it's difficult to tell what you're intending to order by if you come back later. Also, if you need to add a column or change the order, you have to remember to update the ORDER BY clause. It's easy to miss that.

As others mention in the comments, it is possible to use the alias you specify in the ORDER BY. However, because it's a column alias, there's nothing to fully qualify. a.TOTAL1 doesn't mean anything. You must ORDER BY TOTAL1:

select a.Customer
    ,a.PlanTo
    ,a.Dollar01
    ,a.Dollar02
    ,a.Dollar03
    ,a.Dollar04
    ,a.Dollar05
    ,a.Dollar06
    ,a.Dollar07
    ,a.Dollar08
    ,a.Dollar09
    ,a.Dollar10
    ,a.Dollar11
    ,a.Dollar12
    ,(CAST(a.Dollar01 as decimal) + CAST(a.Dollar02 as decimal) 
    + CAST(a.Dollar03 as decimal) + CAST(a.Dollar04 as decimal) 
    + CAST(a.Dollar05 as decimal) + CAST(a.Dollar06 as decimal) 
    + CAST(a.Dollar07 as decimal) + CAST(a.Dollar08 as decimal) 
    + CAST(a.Dollar09 as decimal) + CAST(a.Dollar10 as decimal) 
    + CAST(a.Dollar11 as decimal) + CAST(a.Dollar12 as decimal)) as TOTAL1
from MDM_STAT.sds.SMarginText a
where a.salesyear = '2016'
order by total1

This works because of the query solve order. ORDER BY is solved after the SELECT, unlike WHERE or FROM which are solved before the SELECT and therefore can't refer to column aliases in SQL Server.

This can be confusing or ambiguous if your column alias has the same name as a column from a table, so you need to be aware of that.

Noctis
  • 11,507
  • 3
  • 43
  • 82
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Thanks for the explanation. This was pretty helpful in understanding how the clauses work. Appreciate it. – RB17 Aug 03 '16 at 17:39
  • @RahulBanerjee It is helpful to know the order queries are executed. [Here](http://stackoverflow.com/a/17403990/696808) is greater detail on that, if you're interested. – Bacon Bits Aug 03 '16 at 18:35
5

Posting Aaron Bertrand's comment as a community wiki, as I think it's the most straight forward answer:

a.total1 doesn't exist, since SQL Server will look for that column in SMarginText , but ORDER BY TOTAL1; would work just fine.

sstan
  • 35,425
  • 6
  • 48
  • 66
3

The easiest way would be to use the column's number in the select list instead of its name: ORDER BY 13.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 7
    [Such a terrible idea to recommend, especially for production code, since ordinals are very brittle](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx). In other news, the easiest way to wash your dishes is to throw them away and buy new ones. ("Easiest" and "best" rarely coincide.) – Aaron Bertrand Aug 03 '16 at 17:26
  • Aaron Bertrand, the link to your blog's archive is dead. I don't really need it but you might want to know. – Mosset Jérémie Jun 28 '19 at 14:33