SQL Server Performance

help with a join query to return unique rows

Discussion in 'General Developer Questions' started by thebends, Apr 28, 2008.

  1. thebends New Member

    Hi All,

    I need a bit of help with a join. I have 2 tables :

    has fields : SymbolID, CurrentPrice, TotalValue

    has fields : SymbolID, TradeID, ExecutionTime, TradeValue

    TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID

    and what I want to retreive is :

    For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
    and also get TradeValue from Trades for the row for max(ExecutionTime)
    tables are joined on TradeSummary.SymbolID = Trades.SymbolID

    Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

    thanks in advance
  2. MohammedU New Member

    Try the following...
    select s.CurrentPrice, s.TotalValue, t.TradeValue
    from TradeSummary s
    join Trades t on t.SymbolID = s.SymbolID
    where ExecutionTime = (select max (ExecutionTime) from Trades t1 where t1.SymbolID = t.SymbolID )
  3. thebends New Member

    hey mohammed,
    thanks for the reply...I tried it but it still returns multiple rows for each SymbolD (TradeSummary has 90 rows, I got about 130 results)
  4. thebends New Member

    got it to work by using the unique ID field on Trades :
    select a.SymbolID, a.CurrentPrice, a.TotalValue, t1.TradeValue, t1.Exec_Time
    from TradeSummary a, Trades t1
    where a.SymbolID = t1.SymbolID
    and t1.ID in (select MAX(t2.ID) from Trades t2
    where a.SymbolID = t2.SymbolID)

Share This Page