How sort a table based on age, and extract the top 2 user's address on mysql?-CodePudding

I have a table like this

create table users(
    user_id  int not null auto_increment,
    user_age date,
    user_address varchar(255),
    primary key(user_id)
    )

user_id	user_age	user_address
1	2010-01-05	87 Polk St. Suite 5
2	2010-01-06	Carrera 52 con Ave. Bolivar #65-98 Liano Largo
3	2010-01-07	Ave. 5 de Mayo Porlamar
4	2010-01-08	89 Chiaroscuro Rd.
5	2010-01-09	Via Ludovico il Moro 22
6	2010-01-10	Rue JosephBens 532
7	2011-01-05	43 rue St. Laurent
8	2011-01-06	Heerstr. 22
9	2011-01-07	South House 300 Queensbridge
10	2011-01-08	Ing. Gustavo Moncaa 8585 Piso 20-A
11	2011-01-09	Obere Str. 57
12	2011-01-10	Avda. de la Constitución 2222
13	2012-01-05	Mataderos 2312
14	2012-01-06	120 Hanover Sq.
15	2012-01-07	Berguvsvägen 8
16	2012-01-08	Forsterstr. 57

And I'd like my table to be like this:

user_id	user_age	user_address
1	2010-01-05	87 Polk St. Suite 5
2	2010-01-06	Carrera 52 con Ave. Bolivar #65-98 Liano Largo
7	2011-01-05	43 rue St. Laurent
8	2011-01-06	Heerstr. 22
13	2012-01-05	Mataderos 2312
14	2012-01-06	120 Hanover Sq.

How can I make this happen with group by statement?

CodePudding user response：

If your MySQL supports windows function try:

with cte as 
    ( 
      SELECT *, ROW_NUMBER() OVER(partition by year(user_age) ORDER BY  user_age) row_num 
      FROM users  
      ) 
select user_id,user_age,user_address 
from cte
where row_num<=2;

Demo