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" <to***********@randolph.af.mil> wrote in message
news:9a**************************@posting.google.c om...
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