April 25, 2005 at 4:52 pm
I'm having a hard time figuring this out, can somebody help me? My table structure is as follows:
CREATE TABLE EODDetail ( EODDetailGID int IDENTITY (1, 1) NOT NULL , Symbol varchar (10) NOT NULL , TradeDate datetime NOT NULL , OpenPrice decimal(10, 6) NULL , HighPrice decimal(10, 6) NULL , LowPrice decimal(10, 6) NULL , ClosePrice decimal(10, 6) NULL , Volume int NULL , PercentChange decimal(5, 2) NULL , CONSTRAINT pk_EODDetail PRIMARY KEY CLUSTERED ( Symbol, TradeDate ) )
The table holds daily stock data. Here's what I want to do. I want to get the data for the records with the top 4 PercentChanges per day for the last month. So I would want my data to look something like this:
TradeDate | Symbol | OpenPrice | etc etc | PercentChange |
---|---|---|---|---|
1/1/2005 | MSFT | 100 | 5.25 | |
1/1/2005 | ORCL | 100 | 5.15 | |
1/1/2005 | GOOG | 100 | 5.11 | |
1/1/2005 | YHOO | 100 | 5.01 | |
1/2/2005 | MSFT | 100 | 7.5 | |
1/2/2005 | ORCL | 100 | 7.4 | |
1/2/2005 | GOOG | 100 | 7.1 | |
1/2/2005 | YHOO | 100 | 6.9 | |
1/3/2005 | MSFT | 100 | 11.2 |
Obviously the data presented here is just dummy data, but hopefully it shows the resultset that I'm trying to get. I can figure it out with a cursor, but I know it's possible to do this with a correlated subquery, but just can't seem to get it.
April 26, 2005 at 1:49 am
Hello,
I'm not sure I understood fully your requirement, but here goes... this will produce the same result as you posted, based on supplied data.
SELECT TradeDate, Symbol, OpenPrice, PercentChange
FROM EODDetail eod
WHERE eod.EODDetailGID IN
(SELECT TOP 4 EODDetailGID FROM EODDetail
WHERE TradeDate = eod.TradeDate
ORDER BY PercentChange DESC)
ORDER BY TradeDate, PercentChange DESC
Hope this is it :-). Vladan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply