CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Agent_15x
Agent_15x

Reputation: 383

Stuck on a SQL query

I am running MYSQL 5.1.41 on Ubuntu 10.04.

I have two tables. stocks which contains basic info about a group of stocks and has the following columns: pk, name, pur_date, pur_price, avg_vol, mkt_cap. The other table is data which contains price history about the stocks in the stocks table. The data table contains the following columns: pk, ticker, date, open,high, low, close, volume, adj_close.

I need a query that will show the high for a stock since it's purchase date and the date it occurred.

I have this query:

SELECT ticker, date, MAX(high)
FROM data, stocks
WHERE ticker = sym AND date > pur_date 
GROUP BY  ticker
ORDER BY ticker
LIMIT 0, 100

The query will give me each stocks high but always returns the latest date in the data table, which happens to be 2011-12-23. How do I change the query to show the date the stock reached its high?

Thanks for your help

Upvotes: 2

Views: 158

Answers (2)

DRapp
DRapp

Reputation: 48139

You originally had your grouping by stock ticker, which may not give the correct expected results as I'll explain later. I've actually done a pre-query on a per purchase stock entry and getting the highest price since it was purchased... Consider the following scenario.

Person buys 100 Shares of Stock X on Jan 1 for $20 per share. Buys ANOTHER set of 100 shares on Jan 25 at $23 per share, and another 100 on Feb 18 at $24 per share. The actual data shows Stock X had a high on Jan 22 (before second purchase) of $27 per share then dropped by the time the $23 on Jan 25... shifted down/up from there before Feb purchase, but only on Feb 27 did it kick back up to $25 per share.

The first trade Jan 1 Bought $20 would show a high of $27 on Jan 22 Jan 25 Bought $23 would show the high of $25 on Feb 27 Feb 18 Bought $24 would also show high of $25 on Feb 27

So grouping by ticker might not necessarily show. Now, above scenario being shown, would you want from here that Stock X across-the-board for all purchases showed a high of $27? even though the most recent purchases never saw that price?

select 
      s2.*,
      d2.*
   from 
      ( select 
              s.pk,
              s.sym,
              max( d.high ) as HighSincePurchDay
           from
              stocks s
                 join data d
                    on s.sym = d.ticker
                   AND s.pur_date <= d.date
           group by
              s.pk
      ) PreQuery

      JOIN stocks s2
         on PreQuery.PK = s2.PK

      JOIN data d2
         on PreQuery.Sym = d.Ticker
        AND s.Pur_Date <= d.Date
        AND d.high = PreQuery.HighSincePurchDay

Upvotes: 0

Andomar
Andomar

Reputation: 238086

That's the classic greatest-per-group issue. One solution is to query the maximum high for the ticket in a subquery:

select  ticker
,       date
,       high
from    data d
join    stocks s
on      d.ticker = s.sym
where   d.date > pur_date
        and d.high = 
        (
        select  max(high)
        from    data d2
        where   d2.ticker = d.ticker
               and d2.date > pur_date
        )

See Quassnoi's explain extended blog for a detailed discussion.

Upvotes: 1

Related Questions