Greetings,

I have the follow table structure:

CREATE TABLE [dbo].[Strategies_data] (
[Symbol] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[dateTraded] [smalldatetime] NOT NULL ,
[Strategy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Signal] [smallint] NOT NULL ,
[priceTrigger] [decimal](18, 5) NOT NULL
) ON [PRIMARY]

with some sample data:

Symbol dateTraded Strategy Signal priceTrigger
CSCO 8/5/2003 Reversal -1 19.32
CSCO 7/22/2003 Reversal 1 18.03
CSCO 7/5/2003 Reversal -1 18.30
CSCO 8/4/2003 Swing -1 19.20
CSCO 7/25/2003 Swing 1 18.50
INTC 8/6/2003 Reversal -1 25.30
INTC 7/27/2003 Reversal 1 24.40
INTC 7/22/2003 Swing 1 23.00
INTC 7/06/2003 Swing -1 23.95


I'd like to find the newest row for each symbol/strategy combination.
For example, from the above data, I need this result:

Symbol dateTraded Strategy Signal priceTrigger
CSCO 8/5/2003 Reversal -1 19.32
CSCO 8/4/2003 Swing -1 19.20
INTC 8/6/2003 Reversal -1 25.30
INTC 7/22/2003 Swing 1 23.00


Any help would be appreciated.

TIA

Air coded...

SELECT Symbol, dateTraded, Strategy, Signal, priceTrigger
FROM Strategies_data S
INNER JOIN (
SELECT Symbol, MAX(dateTraded) AS DateTraded, Strategy
FROM Strategies_data
GROUP BY Symbol Strategy) T ON T.Symbol = S.Symbol AND T.dateTraded
= S.DateTraded AND T.Strategy = S.Strategy


"Tom Nunamaker" <tom.nunamaker (AT) randolph (DOT) af.mil> wrote

Fantastic. With only minor corrections, it worked perfectly. That's a nice
technique to use a sub-query as a pseudo table. I haven't done that in a long
time and that certainly did the trick. Here's the working query:

SELECT S.Symbol, S.dateTraded, S.Strategy, Signal, priceTrigger
FROM Strategies_data S INNER JOIN
(SELECT Symbol, MAX(dateTraded) AS DateTraded, Strategy
FROM Strategies_data
GROUP BY Symbol, Strategy) T ON T.Symbol = S.Symbol AND T.dateTraded =
S.DateTraded AND T.Strategy = S.Strategy

Thanks again!




In article <C3aYa.2921$_a4.593671 (AT) news20 (DOT) bellglobal.com>, Yannick Turgeon
says...

Tom,

When I read your post it remembered me one of my first post in this group! I
just looked at it and I posted it exactly 3 years ago: August 6th, 2003! You
can read it here if you'd like:
http://groups.google.com/groups?selm...0SPAMiname.com

Glad it worked without too much changes. I've written it very quickly.


"Tom Nunamaker" <Tom_member (AT) newsguy (DOT) com> wrote

A somewhat different approach using a correlated subquery:

CREATE TABLE [dbo].[Strategies_data]
([Symbol] [varchar] (20) NOT NULL ,
[dateTraded] [smalldatetime] NOT NULL ,
[Strategy] [varchar] (50) NOT NULL ,
[Signal] [smallint] NOT NULL ,
[priceTrigger] [decimal](18, 5) NOT NULL )

insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('CSCO' , '8/5/2003' , 'Reversal' , -1 , 19.32)
insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('CSCO' , '7/22/2003' , 'Reversal' , 1 , 18.03)
insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('CSCO' , '7/5/2003' , 'Reversal' , -1 , 18.30)
insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('CSCO' , '8/4/2003' , 'Swing' , -1 , 19.20)
insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('CSCO' , '7/25/2003' , 'Swing' , 1 , 18.50)
insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('INTC' , '8/6/2003' , 'Reversal' , -1 , 25.30)
insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('INTC' , '7/27/2003' , 'Reversal' , 1 , 24.40)
insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('INTC' , '7/22/2003' , 'Swing' , 1 , 23.00)
insert strategies_data (symbol, datetraded,strategy,signal,pricetrigger)
values('INTC' , '7/06/2003' , 'Swing' , -1 , 23.95)

select sd.* from strategies_data sd
order by
sd.symbol, sd.strategy

gives us this:
Symbol dateTraded
Strategy Signal priceTrigger
-------------------- ------------------------------------------------------
-------------------------------------------------- ------ ------------------
--
CSCO 2003-08-05 00:00:00
Reversal -1 19.32000
CSCO 2003-07-22 00:00:00
Reversal 1 18.03000
CSCO 2003-07-05 00:00:00
Reversal -1 18.30000
CSCO 2003-08-04 00:00:00
Swing -1 19.20000
CSCO 2003-07-25 00:00:00
Swing 1 18.50000
INTC 2003-08-06 00:00:00
Reversal -1 25.30000
INTC 2003-07-27 00:00:00
Reversal 1 24.40000
INTC 2003-07-22 00:00:00
Swing 1 23.00000
INTC 2003-07-06 00:00:00
Swing -1 23.95000

and this:

select sd.*
from strategies_data sd
where sd.datetraded = (select max(datetraded)
from strategies_data sd1
where sd.symbol = sd1.symbol
and sd.strategy = sd1.strategy)
order by
sd.symbol, sd.strategy

give us this:

Symbol dateTraded
Strategy Signal priceTrigger
-------------------- ------------------------------------------------------
-------------------------------------------------- ------ ------------------
--
CSCO 2003-08-05 00:00:00
Reversal -1 19.32000
CSCO 2003-08-04 00:00:00
Swing -1 19.20000
INTC 2003-08-06 00:00:00
Reversal -1 25.30000
INTC 2003-07-22 00:00:00
Swing 1 23.00000

which I think is what you wanted to see.


"Tom Nunamaker" <tom.nunamaker (AT) randolph (DOT) af.mil> wrote