Sub Query Reference Problem

I have the following query:

SELECT
CostName
, Cost
,(
SELECT MAX(price)
FROM Prices
) AS MaxPrice
-- Doesn't work
,(Cost - MaxPrice) AS Profit
-- Works
,(Cost - SELECT MAX(price)
FROM Prices ) AS Profit
From Costs


Which doesn't work idealy I would like to be able to reference the Sub query rather then re-executing it as it is an expensive query any ideas?

You could try something like this :

declare @price int
select @price = max(price) from prices
select costname, (cost - @price) as profit from cost

I'm not sure if this is what you mean because I would suspect you'd want the max price for each individual cost or costname. The above sql just selects the maximum price in the prices table and then subtracts it from each cost.

Is there an individual price for each cost or is it as above, one maximum price in the prices table that needs to be subtracted from each cost?

Cheers
Shaun

World Domination Through Superior Software

The query is simplified from what I'm trying to do basicaly, I'm trying to reference the results of a sub-query with in the main query without resulting to the use of a varible or function. I know that in the order by statement for example, you can refer to the columns ordinaly; but this is not supported within columns themselves

Im a little confused by the way you pasted the query. Could you paste the query which wont compile please?

I suspect what you want could be achieved as simply as a join

SELECT
CostName
, Cost
,(
SELECT MAX(price)
FROM Prices
) AS MaxPrice
,(Cost - MaxPrice) AS Profit -- <-- this line doesn't work
From Costs

I believe that this should work



SELECT CostName,
Cost,
SubTable.MaxPrice
(Cost - SubTable.MaxPrice) AS Profit
From Costs
INNER JOIN (SELECT MAX(price) AS MaxPrice
FROM Prices) SubTable ON 1 = 1