sql语句根据某列值怎么去重？

Question

如图，这是我的表
需求：
查出所有city中最大的times_rank

脚本:

create table day_video_city_access_topn_stat (
day varchar(8) not null,
cms_id bigint(10) not null,
city varchar(20) not null,
times bigint(10) not null,
times_rank int not null,
primary key (day, cms_id, city)
 );

想了半天最后只能做成这样
这是sql语句

   select  cms_id,city,times_rank from `day_video_city_access_topn_stat` 
    group by city,times_rank,cms_id
    order by city,`times_rank` desc

需要怎么写才能只要每个省份的最大times_rank?

Answer 1

用max(times_rank)试下，即：

   select  cms_id,city,max(times_rank) from `day_video_city_access_topn_stat` 
    group by city,cms_id
    order by city,`times_rank` desc