SQL

The following script contains the SQL statements for this query. Note that there are separate scripts optimized for RAPCache and VLDBServer.

VLDBServer

-- Find the most active stocks in the "COMPUTER" industry
-- for the current day.

-- This query is optimized to run on IQ.

commit
;

SELECT st.TRADING_SYMBOL, SUM(TRADE_SIZE) TRADESIZE, 
DENSE_RANK() OVER (ORDER by SUM(TRADE_SIZE) DESC) as RANKING
FROM STOCK_TRADE st 
inner join INSTRUMENT ii
on ii.INSTRUMENT_ID = st.INSTRUMENT_ID
inner join SCND_IDST_CLS sc
on ii.SCND_IDST_CLS_ID = sc.SCND_IDST_CLS_ID
and sc.SIC_NAME = 'COMPUTERS'
WHERE  st.TRADE_DATE = '2005-11-14'
GROUP BY 
st.TRADING_SYMBOL
;

RAPCache

-- Find the most active stocks in the "COMPUTER" industry.


-- This query will run on either the ASE or IQ platform.
set forceplan on
go

SELECT st.TRADING_SYMBOL,SUM(TRADE_SIZE) as TRADESIZE
FROM STOCK_TRADE st 
inner join INSTRUMENT ii
on ii.INSTRUMENT_ID = st.INSTRUMENT_ID
inner join SCND_IDST_CLS sc
on ii.SCND_IDST_CLS_ID = sc.SCND_IDST_CLS_ID
and sc.SIC_NAME = 'COMPUTERS'
WHERE 
TRADE_TIME between '2005-11-14 00:00:00' and '2005-11-14 23:59:59'
GROUP BY st.TRADING_SYMBOL
order by SUM(TRADE_SIZE) DESC

go