How to reference a custom field in SQL

Question

I am using mssql and it is difficult for me to use a subquery. The real request is quite complex, but it has the same structure as this one:

select customerName, customerId, ( select count(*) from Purchases where Purchases.customerId=customerData.customerId ) as numberTransactions from customerData 

And what I want to do is to sort the table by the number of transactions, but when I use

 order by numberTransactions 

This tells me that there is no such field. Is it possible to do this? Should I use some special keyword like this or self ?

Answer 1

use the field number, in this case:

 order by 3 

Answer 2

Sometimes you have to deal with SQL syntax (expected amount of sentences)

 SELECT * FROM ( select customerName, customerId, ( select count(*) from Purchases where Purchases.customerId=customerData.customerId ) as numberTransactions from customerData ) as sub order by sub.numberTransactions 

In addition, the solution using JOIN is correct. Look at the query plan, SQL Server should give the same plans for both solutions.

Answer 3

Make an inner join. It is much easier and more readable.

 select customerName, customerID, count(*) as numberTransactions from customerdata c inner join purchases p on c.customerID = p.customerID group by customerName,customerID order by numberTransactions 

EDIT: Hey Nathan,

Do you understand that you can combine this whole table as a right on the right?

 Select T.*, T2.* From T inner join (select customerName, customerID, count(*) as numberTransactions from customerdata c inner join purchases p on c.customerID = p.customerID group by customerName,customerID ) T2 on T.CustomerID = T2.CustomerID order by T2.numberTransactions 

Or, if this is not good, you can create your queries using temporary tables (# T1, etc.)

Answer 4

There are better ways to get the result, but only from your sample query will this work on SQL2000 or higher.

If you wrap your alias in single ticks of 'numberTransactions' , then call ORDER BY 'numberTransactions'

 select customerName, customerId, ( select count(*) from Purchases where Purchases.customerId=customerData.customerId ) as 'numberTransactions' from customerData ORDER BY 'numberTransactions' 

Answer 5

The same can be achieved using GROUP BY and JOIN , and you will get rid of the subquery. It could be faster.

Answer 6

I think you can do it in SQL2005, but not in SQL2000.

Answer 7

You need to duplicate your logic. SQL Server is not very smart in the columns you named, but are not part of the dataset in your FROM statement.

Therefore use

 select customerName, customerId, ( select count(*) from Purchases p where p.customerId = c.customerId ) as numberTransactions from customerData c order by (select count(*) from purchases p where p.customerID = c.customerid) 

Also use aliases, they make it easier to read and maintain code .;)