<p>I am using mssql and am having trouble using a subquery. The real query is quite complicated, but it has the same structure as this:</p>

<pre><code>select 
  customerName, 
  customerId,
  (
    select count(*) 
    from Purchases 
    where Purchases.customerId=customerData.customerId
  ) as numberTransactions
from customerData
</code></pre>

<p>And what I want to do is order the table by the number of transactions, but when I use</p>

<pre><code>order by numberTransactions
</code></pre>

<p>It tells me there is no such field. Is it possible to do this? Should I be using some sort of special keyword, such as <code>this</code>, or <code>self</code>?</p>

use the field number, in this case:
```
```
order by 3
```
```