Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2008
    Posts
    11

    Question Unanswered: help with a join that needs to return unique records

    Hi All,

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

    TradeSummary
    has fields : SymbolID, CurrentPrice, TotalValue

    Trades
    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 record 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. #2
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    SELECT A.SymbolID, A.CurrentPrice, A.TotalValue, Max(B.ExecutionTime)
    FROM TradeSummary A
    INNER JOIN Trades B ON A.SymbolID = B.SymbolID
    GROUP BY A.SymbolID, A.CurrentPrice, A.TotalValue

    *whoops; just noticed you want TradeValue as well

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT s.SymbolID
         , s.CurrentPrice
         , s.TotalValue
         , t.TradeID
         , m.MaxExecutionTime
         , t.TradeValue
      FROM TradeSummary AS s
    INNER
      JOIN ( SELECT SymbolID
                  , MAX(ExecutionTime) AS MaxExecutionTime
               FROM Trades
             GROUP 
                 BY SymbolID ) AS m
        ON m.SymbolID = s.SymbolID
    INNER
      JOIN Trades AS t
        ON t.SymbolID = s.SymbolID
       AND t.ExecutionTime = m.MaxExecutionTime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    I haven't tested this, but a simple correlated subquery should work. Try this and let me know if it works:

    Code:
    SELECT ts.CurrentPrice, ts.TotalValue, t1.TradeValue
    FROM tradesummary ts,
             trades t1
    WHERE ts.symbolid = t1.symbolid
    AND t1.ExecutionTime in
      (SELECT MAX(t2.ExecutionTime)
        FROM trades t2
        WHERE ts.symbolid = t2.symbolid);

  5. #5
    Join Date
    Apr 2008
    Posts
    11
    Quote Originally Posted by buckeye234
    I haven't tested this, but a simple correlated subquery should work. Try this and let me know if it works:

    Code:
    SELECT ts.CurrentPrice, ts.TotalValue, t1.TradeValue
    FROM tradesummary ts,
             trades t1
    WHERE ts.symbolid = t1.symbolid
    AND t1.ExecutionTime in
      (SELECT MAX(t2.ExecutionTime)
        FROM trades t2
        WHERE ts.symbolid = t2.symbolid);
    Thanks - This nearly works except that it returns multiple rows per symbolid. I want to get one row for each symbolid.

    I probably should add that ExecutionTime is not unique - you can have more than one Trades for the same symbolid with the same ExecutionTime

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by thebends
    I probably should add that ExecutionTime is not unique - you can have more than one Trades for the same symbolid with the same ExecutionTime
    oh hai

    that's wonderful

    so why don't we take the one with the highest value from all the ones with the same time

    would that work?

    oh noes!!1!!

    what if two trades for the same symbol happen at the same time for the same value?

    perhaps you could explain why you want only one trade?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Quote Originally Posted by r937
    oh noes!!1!!

    LOL

    -A

  8. #8
    Join Date
    Apr 2008
    Posts
    11
    Finally got it to work, here's the correct sql - I am using the unique+increment field Trades.ID to return the newest Trade for a given SymbolID :

    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)

  9. #9
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Quote Originally Posted by thebends
    Thanks - This nearly works except that it returns multiple rows per symbolid. I want to get one row for each symbolid.
    How do you differentiate between two trades for the same symbolid at the same time? Use that criteria and modify the SQL statement to account for it.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    using an IDENTITY column to distinguish insertion sequence is a mistake waiting to bite you you know where

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2008
    Posts
    11
    see my reply above yours - I use max(ID) to return the 'newest' Trade and don't use Exec_Time anymore.

    thanks a million for your help

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    max(ID) is not always going to give you the "latest" one

    oh, it might look like it does, and it could go humming along nicely for months, until one day suddenly it doesn't work correctly

    when that day comes, remember this thread and smile

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •