SQL Stumper

• SumOfDavid

  Ten Centuries

  Points: 1357

  More actions

  April 25, 2005 at 4:52 pm

  #92943

  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.

   

  ---

  David

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  April 26, 2005 at 1:49 am

  #554775

  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