sql分组取最大值的方法

按name分组取val最大的值所在行的数据。

–方法1:

select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name

–方法2:

select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)

–方法3:

select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name

–方法4:

select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name

–方法5

select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name

sql分组后按时间取分组中的最新值

消息按人分组,且显示用户发送过来的最新消息

select a.* from job_message a where mid= (select mid from job_message where send_uid = a.send_uid order by send_date desc limit 1)  and to_uid=’10028′