How to select only the rows with the latest date for each stock

Question

stock data

ticker  date         open   high    low     close   volume
--------------------------------------------------------------
AAA     2014-06-16   0.9    0.9     0.89    0.895   542
AAA     2014-06-15   1.32   1.33    1.31    1.32    2792
AAA     2014-06-14   1.19   1.25    1.19    1.25    417
AAA     2014-06-13   0.715  0.715   0.705   0.71    36526

BBB     2014-06-16   0.9    0.9     0.89    0.895   542
BBB     2014-06-15   1.32   1.33    1.31    1.32    2792
BBB     2014-06-14   1.19   1.25    1.19    1.25    417
BBB     2014-06-13   0.715  0.715   0.705   0.71    36526

CCC     2014-06-16   0.9    0.9     0.89    0.895   542
CCC     2014-06-15   1.32   1.33    1.31    1.32    2792
CCC     2014-06-14   1.19   1.25    1.19    1.25    417
CCC     2014-06-13   0.715  0.715   0.705   0.71    36526

I want to select so that it only returns the rows with the latest dates(dynamically), the results with 2014-06-16 for each stock

AAA     2014-06-16   0.9    0.9     0.89    0.895   542
BBB     2014-06-16   0.9    0.9     0.89    0.895   542
CCC     2014-06-16   0.9    0.9     0.89    0.895   542

This looks like a duplicate of http://stackoverflow.com/questions/2411559/how-do-i-query-sql-for-a-latest-record-date-for-each-user?rq=1 — Frazz, Jun 17 '14 at 08:53

score 1 · Accepted Answer · answered Jun 17 '14 at 08:55

1

select Stock_data.* 
from Stock_data inner join ( 
   select ticker, max(date) maxdate 
   from Stock_Data 
   group by ticker) maxdates 
on maxdates.maxdate=date 
and maxdates.ticker=Stock_data.ticker

answered Jun 17 '14 at 08:55

MikkaRin

3,026
19
34

score 0 · Answer 2 · answered Jun 17 '14 at 08:56

Try this

SELECT T.ticker,T.date,T.open,T.high,T.low,T.close,T.volume
FROM Table1 T
     INNER JOIN (
           SELECT ticker,Max(date) as MaxDate
           FROM Table1
          GROUP BY ticker
                ) S ON S.ticker = T.ticker AND T.date = S.MaxDate

score 0 · Answer 3 · answered Jun 17 '14 at 08:56

0

Query like following can be used : select d.ticker, MAX(d.date) from stock_data d group by d.ticker

answered Jun 17 '14 at 08:56

Pranav Maniar

1,545
10
22

score 0 · Answer 4 · answered Jun 17 '14 at 09:00

Try below query:

WITH TAB AS (
    SELECT
        TICKER,
        DAY,
        OPEN,
        HIGH,
        LOW,
        CLOSE,
        VOLUME, 
        RANK() OVER(PARTITION BY TICKER 
                          ORDER BY DAY DESC) AS RANKK
      FROM Stocks)
SELECT TICKER, DAY,   OPEN,    HIGH,     LOW,     CLOSE,     VOLUME
FROM TAB 
WHERE TAB.RANKK = 1

score 0 · Answer 5 · edited Jun 17 '14 at 11:48

0

using Natural join will do the trick :

select * from 
table_name 
natural join (select ticker, max(date) as date 
          from table_name
          group by ticker);

edited Jun 17 '14 at 11:48

VahidNaderi

2,448
1
23
35

answered Jun 17 '14 at 11:26

Sachin Shetty

11
2

How to select only the rows with the latest date for each stock

5 Answers5