CODEWITHSUNDEEP
sqlsql-servert-sqlcastingsql-order-by
Eric T
Eric T

Reputation: 19

T-SQL Order by doesn't work with Cast in MSSQL

Can't seem to get the order by to work with cast. Can someone help?

Error message is:

Invalid column name NewPrice

Select top 100 ma.*,
               (Select Top (1) Price from [dbo].[ItemSKU] Where ma.OldSku = NewSKu) as NewPrice
from [dbo].[2018] as ma
order by cast (NewPrice as decimal(10,2)) desc

Upvotes: 2

Views: 831

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This is a subtle problem. As the documentation explains:

Specifies a column or expression on which to sort the query result set. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the column in the select list.

What is left out is that the expression could use a column alias. That is subtle, but a limitation (and one not present in most other databases).

I think the simplest solution is to do the cast() in the subquery. If you want to order by the value, isn't that what you want to see?

Select top 100 ma.*,
       (Select Top (1) cast(i.Price as decimal(10, 2))
        from [dbo].[ItemSKU] i
        Where ma.OldSku = i.NewSKu
       ) as NewPrice
from [dbo].[2018] ma
order by NewPrice desc;

Notice that I also added table aliases and qualified column names into the query. This is a best practice whenever your query refers to more than one table.

Upvotes: 0

maulik kansara
maulik kansara

Reputation: 1107

You are getting NewPrice values using subquery, therefore, you can not use cast with orderby for a computed column in the same query. The simplest way could be like below.

Solution 1

select * from (
Select top 100 ma.*,
               (Select Top (1) Price from [dbo].[ItemSKU] Where ma.OldSku = NewSKu) as NewPrice
from [dbo].[2018] as ma
)a order by cast(NewPrice as decimal(10,2)) desc

Solution 2

Select top 100 ma.*,
               cast((Select Top (1) Price from [dbo].[ItemSKU] Where ma.OldSku = NewSKu)as decimal(10,2)) as NewPrice
from [dbo].[2018] as ma
order by NewPrice desc

Upvotes: -1

Michał Turczyn
Michał Turczyn

Reputation: 37367

The reason is the scope of the column. You introduce column at the same time as you are using it. It's not allowed in SQL Server.

To be clear - you can use alias of an column in an ORDER BY, but the issue here is that you are using it inside CAST function - that's the problem.

Right syntax would be (this solution is by changing the scope):

SELECT TOP 100 * FROM (
    SELECT ma.*,
           (SELECT TOP (1) Price FROM [dbo].[ItemSKU] WHERE ma.OldSku = NewSKu) AS NewPrice
    FROM [dbo].[2018] AS ma
) a ORDER BY CAST(NewPrice AS DECIMAL(10,2)) DESC

or (here we do all operations needed to order in column list, so you can reference aliased column in an ORDER BY)

SELECT TOP 100 ma.*,
       CAST((SELECT TOP (1) Price FROM [dbo].[ItemSKU] WHERE ma.OldSku = NewSKu) AS DECIMAL(10, 2)) AS NewPrice
FROM [dbo].[2018] AS ma
ORDER BY NewPrice

Upvotes: 3

Related Questions