Database ranking SQL, group by group query by maximum time, sqlgroup

Database ranking SQL, group by group query by maximum time, sqlgroup

First, give a simple table similar

Drop table if exists 'toutiaoanchor'; create table 'toutiaoanchor' ('urid' int (10) unsigned not null, 'betintime' varchar (40) not null, 'anchoruid' int (11) unsigned not null, 'ticket' int (10) unsigned not null default '0' comment' shining value', 'pic 'varchar (500) DEFAULT '', 'info' varchar (128) not null, 'start' int (11) not null comment 'Star value', 'hitnum' int (11) not null comment 'number of consecutive hits, accumulative for the last time, otherwise accumulated again ', 'Week 'Int (11) not null comment' week ', 'flag' int (11) not null comment' indicates whether it is used for sorting. 1 indicates ', primary key ('urid') ENGINE = InnoDB default charset = utf8;

Currently, ranking is based on group by. Each ranking is based on the field of the first record in each group,

For example

select AnchorUid,pic,SUM(star)as starNum,BetinTime from  TouTiaoAnchor  GROUP BY AnchorUid  ORDER BY starNum DESC,BetinTime asc LIMIT 0,3

At this time, betintIme is the time of the first record. However, I want to use the time of the latest record.

Therefore, I cannot achieve what I want.

After analysis, group by is the first data. So I will give it to him again.

At this time, we need to use a new table.

select AnchorUid,pic,SUM(star)as starNum,BetinTime from (select * from TouTiaoAnchor ORDER BY BetinTime DESC ) tt GROUP BY AnchorUid  ORDER BY starNum DESC,BetinTime asc LIMIT 0,3

In this way, the problem is solved.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.