Pull Rows That Equal Max Date

Question

My table structure is below. The ma_symbol and ma_date are primary keys for this table.

ma_symbol |    ma_date | ma
        A   2015-03-01    1
        A   2016-04-01    2
        B   2014-01-01    3
        B   2014-02-02    4

For each ma_symbol, I want to pull the rows where ma_date equals max(ma_date). So the result would look like this from the above example:

ma_symbol |    ma_date | ma
        A   2016-04-01    2
        B   2015-02-02    4

I have tried to adapt the following example but it takes far too long to run. This table has 5 million + records.

Attempted Adapted Query

Please post the query you've attempted (after adapting it from the linked post). The query in the linked post is irrelevant. Post your effort to query the table; ;we can't tell you what you might do differently in your code when you don't include your code. — Ken White, Mar 24 '16 at 02:08

score 0 · Accepted Answer · answered Mar 24 '16 at 02:06

Try this:

SELECT t1.*
FROM tbl t1 INNER JOIN 
    (SELECT ma_symbol, MAX(ma_date) AS max_ma_date FROM tbl GROUP BY ma_symbol) t2
    ON t1.ma_symbol = t2.ma_symbol AND t1.ma_date = t2.max_ma_date;

To run this query in your enviroment, modify tbl to your real table name.

Pull Rows That Equal Max Date

1 Answers1