Getting latest tagname when aggregating data

[azzazzello]

I have the following query:

mysql> desc tradesTable;
+--------------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| date | date | NO | PRI | NULL | |
| account_id | int(16) | NO | PRI | NULL | |
| stock_id | varchar(32) | NO | PRI | NULL | |
| symbol | varchar(32) | NO | | NULL | |
| trades | int(40) | NO | | 0 | |
+--------------+-------------+------+-----+-------------------+-------+

I wish to aggregate trades across a date range and present it by account_id and stock_id. Stock_id uniquely identifies a stock, whereas "symbol" is just what it is called now. So symbol can change, but stock_id never will. I would also like to have the LATEST symbol name (by date) in this rollup. I am doing the following:

select account_id,stock_id,symbol,sum(trades) 
from tradesTable
where date between 20110101 and 20110301
group by account_id,stock_id

But whether I get the latest symbol or not seems arbitrary. How can I force it to be the latest, whenever that might be. This table is rather large (40M+ rows)

 

[r937]

latest, whenever that may be, even if it's later than the date range specified?

okay

SELECT date_range.account_id
     , date_range.stock_id
     , grab_symbol.symbol
     , SUM(date_range.trades) AS sum_trades
  FROM ( SELECT account_id
              , stock_id
              , MAX(date) AS latest
           FROM tradesTable
         GROUP
             BY account_id
              , stock_id ) AS m
INNER
  JOIN tradesTable AS grab_symbol
    ON grab_symbol.account_id = m.account_id
   AND grab_symbol.stock_id = m.stock_id
   AND grab_symbol.date = m.latest
INNER
  JOIN tradesTable AS date_range
    ON date_range.account_id = m.account_id
   AND date_range.stock_id = m.stock_id
   AND date_range.date BETWEEN 20110101 AND 20110301
GROUP 
    BY date_range.account_id
     , date_range.stock_id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[azzazzello]

Rudy,

Well, it works, but because the table is huge, it makes the query about 6 times as slow. I am doing the following for data:

sum(case when date_range.date = 20110419 then date_range.trades else 0 end) as daytrades
, sum(case when date_range.date >= 20110401 and date_range.date <= 20110419 then date_range.trades else 0 end) as mtdtrades
, sum(case when date_range.date >= 20110101 and date_range.date <= 20110419 then date_range.trades else 0 end) as ytdtrades

I wonder if there is something like this that I can do for symbol as well...
In addition, this join is very VERY clever - too clever for someone maintaining it to easily come in and understand what it is it's doing and why.