Select only last record of distinct values

Question

I have a table like that

| Symbol     | Value       | created_at   |
|:-----------|------------:|:------------:|
| A          |      A1     |  01/01/1970  |
| A          |      A2     |  01/01/2020  |
| B          |      B1     |  01/01/1970  |
| B          |      B2     |  01/01/2020  |
| C          |      C1     |  01/01/1970  |
| C          |      C2     |  01/01/2020  |

I need to query only the last record ( sorted by created_at ) of each symbol in the table Expected output is this :

| Symbol     | Value       | created_at   |
|:-----------|------------:|:------------:|
| A          |      A2     |  01/01/2020  |
| B          |      B2     |  01/01/2020  |
| C          |      C2     |  01/01/2020  |

I have no idea how I can achieve that, do you have some suggestions? Thanks you !

Answer 1

One option is to filter with a subquery:

select t.*
from mytable t
where t.created_at = (
    select max(t1.created_at) from mytable t1 where t1.symbol = t.symbol
)

This query would take advantage of an index on (symbol, created_at).

If you are running MySQL 8.0, you can also use row_number():

select t.*
from (
    select t.*, row_number() over(partition by symbol order by created_at desc) rn
    from mytable 
) t
where rn = 1

Answer 2

with t as
(
  select *, row_number() over(PARTITION BY Symbol ORDER BY created_at DESC) as rn
  from your_table
)
select * from t
where rn = 1

Answer 3

You can use windowing functions (something like the following should work although I haven't tested it):

select *, row_number() over(partition by symbol order by created_at desc) as rownum where rownum = 1