help with a join to return unique records

Question

help with a join to return unique records

sherif_omar

SSC Enthusiast

Points: 100
More actions
April 28, 2008 at 2:14 pm

#71160

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

How about

SELECT ts.SymbolID, MAX(t.ExecutionTime) FROM TradeSummary ts LEFT JOIN Trades t ON ts.SymbolID=t.SymbolID GROUP BY t.SymbolID

John Rowan SSC Guru Points: 56440 More actions · Answer 2

If SQL Oracle's answer does not do it for you, you'll want to give us some more info such as sample data, expected results, and how 'bout posting the queries that you've tried?

http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

sherif_omar SSC Enthusiast Points: 100 More actions · Answer 3

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)