mysql group by 组内排序

一：怎样查看每个分组内（根据某列排序）的最后一条记录

有数据表 comments

------------------------------------------------
| id | newsID | comment | theTime |
------------------------------------------------
| 1  |        1      |         aaa    |     11       |
------------------------------------------------
| 2  |        1      |         bbb    |     12       |
------------------------------------------------
| 3  |        2      |         ccc     |     12       |

------------------------------------------------

newsID是新闻ID，每条新闻有多条评论comment，theTime是发表评论的时间

现在想要查看每条新闻的最新一条评论：

select * from comments group by newsID 显然不行

select * from comments group by newsID order by theTime desc 是组外排序，也不行

下面有两种方法可以实现：

（1）
selet tt.id,tt.newsID,tt.comment,tt.theTime from(  
select id,newsID,comment,theTime from comments order by theTime desc) as tt group by newsID 

（2）
select id,newsID,comment,theTime from comments as tt group by id,newsID,comment,theTime having

 theTime=(select max(theTime) from comments where newsID=tt.newsID)

二：怎样查看分组内（按照某个字段排序)的第几条记录

employee有如下记录：

SELECT a.* 
FROM
(
SELECT t1.*,(SELECT COUNT(*)+1 FROM employee WHERE deptno=t1.deptno   AND createtime<t1.createtime ) AS group_id
FROM employee t1
ORDER BY deptno,createtime
) a
WHERE a.group_id=2

结果如图：

不用where条件限制时的结果如下：

SELECT *
FROM (
SELECT IF(  
     (t1.deptno = @page),  
     @row := @row + 1,  
     @row := 1  
    )rownum,@page := t1.deptno pn ,t1.*
FROM ((SELECT * FROM employee ORDER BY deptno,createtime)t1 ,(SELECT  
     @row := 0,  
     @page := '000000') row1) )cc
WHERE rownum=2

结果如图：

三：怎样查看每个分组内的第一条记录（默认根据插入记录的实际创建时间升序排序）

USE dba;
CREATE TABLE employee(id INT,deptno INT,NAME VARCHAR(30));
INSERT INTO employee(id,deptno,NAME) VALUES(1,10,'dan'),(2,10,'jiao'),(3,20,'song'),(4,20,'yang'),(5,30,'dd');
ALTER TABLE employee  ADD createtime DATETIME;
UPDATE employee SET createtime='2016-09-05' WHERE id = 1;
UPDATE employee SET createtime='2016-08-05' WHERE id = 2;
UPDATE employee SET createtime='2016-09-04' WHERE id = 3;
UPDATE employee SET createtime='2016-09-05' WHERE id = 4;
UPDATE employee SET createtime='2016-09-05' WHERE id = 5;
INSERT INTO employee(id,deptno,NAME,createtime) VALUES(6,20,'aa','2016-05-08');

虽然deptno=10中createtime='2016-09-05'大于createtime='2016-08-05'，但是由于是最初创建的，所以排在了前面，显示出来。

--本篇文章参考了：mysql group by 组内排序，mysql分组后组内排序