【SQL】【mysql/hive】根据某字段排序后取第一行数据

【hive 语法】可以根据row_number()over()语法获取特定分组下某个字段排序后的第一行数据

select  *  from
 (
SELECT name, sex, row_number() over (partition by name ORDER by age DESC) AS num
FROM tableA  
) a
    WHERE  a.num=1

【mysql语法】因为mysql不支持rank over , row_number over等语法，所以需要曲线救国

select a.*, 
       b.status,
       b.add_time 
from (
    select id, name 
    from tableA
 ) a 
 left join ( 
    select id,
           status,
           max(a.add_time) as add_time
    from ( 
       select id, 
              status,
              add_time 
        from tableB order by add_time desc limit 10000000  
     ) a group by id)
  b on a.id=b.id