Visual inspection of the first sentence on the first floor is wrong. There is no guarantee of different topics. Here is what I wrote:
select * from posts p where p.dateline = (select max(dateline) from posts where tid = p.tid) order by dateline desc limit 10;
In fact, dateline and id should be in the same order. You can consider changing to id. The primary key on id should be more efficient:
select * from posts p where p.id = (select max(id) from posts where tid = p.tid) order by id desc limit 10;
In the second sentence, I personally think it is better not to use ORDER BY RAND(). A slightly larger amount of data will be inefficient, because writing this way may lead to multiple scans. It may be better to write it this way:
SELECT * FROM posts WHERE id >= ((SELECT MAX(id) FROM posts)-(SELECT MIN(id) FROM posts)) * RAND() + (SELECT MIN(id) FROM posts) LIMIT 1;
Try it. If the table data is too large, it is not recommended to use ORDER BY RAND(). Please try these two sentences SQL first to see if they work. Hope it helps you.
SELET `id`,`tid`,`subject`,`message`,`dateline` FROM `posts` WHERE `tid`=NULL ORDER BY `tid`,`dateline`
SELET `id`,`tid`,`subject`,`message`,`dateline` FROM `posts` WHERE `tid`=!NULL ORDER BY RAND() LIMIT 1
The first answer upstairs is not correct by visual inspection. The following is my personal opinion. Comments and advice are welcome
select t2.* from (select tid,max(dateline) maxDateline from posts group by tid order by maxDateline desc limit 10)t1 inner join posts t2 on t1.tid=t2.tid and t1.maxDateline=t2.dateline
Visual inspection of the first sentence on the first floor is wrong. There is no guarantee of different topics. Here is what I wrote:
In fact, dateline and id should be in the same order. You can consider changing to id. The primary key on id should be more efficient:
In the second sentence, I personally think it is better not to use ORDER BY RAND(). A slightly larger amount of data will be inefficient, because writing this way may lead to multiple scans. It may be better to write it this way:
Try it. If the table data is too large, it is not recommended to use
ORDER BY RAND()
. Please try these two sentencesSQL
first to see if they work. Hope it helps you.The first answer upstairs is not correct by visual inspection. The following is my personal opinion. Comments and advice are welcome