ORDER BY clause (Field) conflicts with DISTINCT. Question

[IEAN]

I have an SQL statement that looks something like this:

SELECT DISTINCT ItemID, Brand, PriceA*1.1 AS PriceB
FROM InventoryTable
Order By PriceB

When I run the SQL above I get an error that looks something like this:

ORDER BY clause (PriceB) conflicts with DISTINCT.

I did some research on this problem and it seems like I cannot do a ORDER BY on a calculated field inside a Distinct clause. Is that correct? If so is there anyway I can work around this? I really need to get this to work and I appreciate any help I can get on this, thank you in advance for your help!

 

[Sheco]

This doesn't really seem like an ASP question...

But anyway, if PriceA is multiplied by a constant value... that is to say all rows are multiplied by the same amount... well then why not just ORDER BY on PriceA?

Maybe I can draw a picture of what I'm trying to say....
[tt]
ItemID FieldA FieldB
------ ------ ------
WXYZ 80.00 88.00
ABC1 100.00 110.00
QQQQ 123.45 135.80
ABC2 150.00 165.00
[/tt]

ok so see if Field B is 10% more than Field A then you can sort by either A or B and the result will be in the same order.

 

[IEAN]

Hello Sheco, thank you for your reply, logically that make sense perfectly, but I have tried both PriceA and PriceB, unfortunately they both throw the same error, I don't know why, its strange... Plus I can't sort by PriceA because PriceA is not exactly Distinct even if it is for the same ItemID(the same ItemID has multiple prices), so I am trying to find a way to sort PriceB. Any ideas?

 

[Tarwn]

Why are you using distinct? How many cases do you have in the database where a record has the same exact ItemID, Brand, and Price? Is it possible you don't need it?

If your looking to only get a single price back for an ItemID andbrand, you might be better off doing something like:
SELECT ItemID, Brand, MAX(PriceA * 1.1) as PriceB
FROM InventoryTable
GROUP BY ItemID, Brand
ORDER BY PriceA

-T

 

[barny2006]

SELECT DISTINCT ItemID, Brand, PriceA*1.1 AS PriceB""
don't forget that DISTINCT applies not only to idemID but to everything followed by DISTINCT. example

itemID ffielda fieldb
====== ======= =======
abc 10.00 20.00
abc 10.00 30.00
both of these rows are distinct applying the distinct to all three fields.