In MySQL: fetching rows distinct by year

Question

I have a MySQL table similar to this:

| id | name | create_date |
---------------------------
| 1  | foo  | 2003-03-11  |
| 2  | goo  | 2003-04-27  |
| 3  | woo  | 2004-10-07  |
| 4  | too  | 2004-12-01  |
| 5  | hoo  | 2005-04-20  |
| 6  | koo  | 2006-01-12  |
| 7  | boo  | 2006-04-17  |
| 8  | moo  | 2006-08-19  |

I want to fetch all the latest yearly rows - one per year. So in the example above I'll get 2, 4, 5 and 8.

What's the right syntax?

Answer 1

you can do something like

select * from table_name where create_date in ( select max(create_date) from table_name group by year(create_date))

Answer 2

SELECT id FROM foo JOIN
  (SELECT YEAR(create_date),MAX(create_date) AS md
     FROM foo
     GROUP BY YEAR(create_date)) as maxes
ON (create_date=md);

If you put an index on create_date, this will be fairly fast.

Answer 3

SELECT  mi.*
FROM    (
        SELECT  DISTINCT YEAR(created_date) AS dyear
        FROM    mytable
        ) md
JOIN    mytable mi
ON      mi.id =
        (
        SELECT  id
        FROM    mytable ml
        WHERE   ml.create_date < CAST(CONCAT_WS('.', dyear + 1, 1, 1)) AS DATETIME)
        ORDER BY
                ml.create_date DESC
        LIMIT 1
        )

Answer 4

Some of the other answers may work for you but this simple query does not require any joins

SELECT YEAR(create_date),
(SELECT id ORDER BY create_date DESC LIMIT 1)
FROM mytable
group by YEAR(create_date)

Answer 5

select id
from mytable
where not exists (
  select * from mytable as T2
  where T2.id = mytable.id
  and T2.id >= year(created_date) + 1
)