Join and return unique records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join and return unique records

Author

Topic

thebends
Starting Member

11 Posts

Posted - 2008-04-28 : 16:10:30

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

thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 16:17:36

Something like this should do it:


SELECT ts.SymbolID, ts.CurrentPrice, ts.TotalValue, t.TradeValue
FROM TradeSummary ts
INNER JOIN
(
	SELECT SymbolID, TradeValue, MAX(ExecutionTime) AS ExecutionTime
	FROM Trades
	GROUP BY SymbolID, TradeValue
) t
ON ts.SymbolID = t.SymbolID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thebends
Starting Member

11 Posts

Posted - 2008-04-28 : 17:11:39

Hi Tara, thanks for the reply.

I ran the sql you provided and got this error :

No column was specified for column 3 of 't'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 17:17:55

Post what you tried as I don't see how you could get that error with my code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thebends
Starting Member

11 Posts

Posted - 2008-04-28 : 17:33:37

select ts.EquityID, ts.NumberofTrades, ts.ClosingPrice , t.Trade_Price, t.Exec_Time
from TradeSummary ts
inner join (
Select Symbol_code, Trade_price, max(Exec_Time)
from Trades
group by Symbol_code, Trade_price, Exec_Time
) t
ON ts.EquityID = t.Symbol_code

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 17:36:00

Now compare your query to mine. You'll notice they are different besides just the column name changes.

First you shouldn't be grouping on Exec_Time. You aren't going to get the correct results by doing that.
Second, you left off the alias for max(Exec_Time).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thebends
Starting Member

11 Posts

Posted - 2008-04-28 : 17:37:58

sorry i was missing a bit - fixed it as :

select ts.EquityID, ts.NumberofTrades, ts.ClosingPrice , t.Trade_Price, t.Exec_Time
from TradeSummary ts
inner join (
Select Symbol_code, Trade_price, max(Exec_Time) as Exec_Time
from Trades
group by Symbol_code, Trade_price, Exec_Time
) t
ON ts.EquityID = t.Symbol_code

but the result set is not as expected - tradesummary has 160 rows, trades > 30k rows - i get 23k rows returned.

I expect 160.

ramkim
Starting Member

1 Post

Posted - 2008-04-28 : 17:38:34

you missed alias name for max(Exec_Time)

select ts.EquityID, ts.NumberofTrades, ts.ClosingPrice , t.Trade_Price, t.Exec_Time
from TradeSummary ts
inner join (
Select Symbol_code, Trade_price, max(Exec_Time) as Exec_Time
from Trades
group by Symbol_code, Trade_price, Exec_Time
) t
ON ts.EquityID = t.Symbol_code

quote:
Originally posted by thebends

select ts.EquityID, ts.NumberofTrades, ts.ClosingPrice , t.Trade_Price, t.Exec_Time
from TradeSummary ts
inner join (
Select Symbol_code, Trade_price, max(Exec_Time)
from Trades
group by Symbol_code, Trade_price, Exec_Time
) t
ON ts.EquityID = t.Symbol_code

Nothing is too high if one can reach it

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 17:39:57

quote:
Originally posted by thebends

but the result set is not as expected - tradesummary has 160 rows, trades > 30k rows - i get 23k rows returned.

I expect 160.

See my first point in my last post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thebends
Starting Member

11 Posts

Posted - 2008-04-28 : 17:40:31

sorry again for the multiple messages :) I fixed the alias and removed the grouping - but still get more results than expected (now they're 5k)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 18:08:22

We'll need to see sample data then. Show us what the data in the tables look like and then show us which rows shouldn't be in your result set. The key to this is just showing us samples.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

thebends
Starting Member

11 Posts

Posted - 2008-04-29 : 08:19:32

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)

thanks again everybody.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources