SQL Server Performance

Sub Query Reference Problem

Discussion in 'T-SQL Performance Tuning for Developers' started by BigJimSlade, Mar 31, 2003.

  1. BigJimSlade New Member

    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?
  2. trifunk New Member

    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
  3. BigJimSlade New Member

    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
  4. Chappy New Member

    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
  5. BigJimSlade New Member

    SELECT
    CostName
    , Cost
    ,(
    SELECT MAX(price)
    FROM Prices
    ) AS MaxPrice
    ,(Cost - MaxPrice) AS Profit -- <-- this line doesn't work
    From Costs
  6. Dave Wells New Member

    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

Share This Page