对于这样的sql需求:按照某个字段分组排序,选出每组里第一条数据进行展示,可用如下方法达成。
- Mysql:
SELECT vin, SUBSTRING_INDEX(GROUP_CONCAT(status ORDER BY update_time desc),',',1),
MAX(update_time)
FROM tr_party_account_role
WHERE role_code = 'primary_user' GROUP BY vin;
- Oracle,Hive,Impala:
SELECT vin,party_id,status,cast(create_time as string) create_time, cast(update_time as string) update_time,
row_number() over (PARTITION BY vin ORDER BY create_time desc,update_time desc) as row_no
FROM mos.sa_mos_tcrm_mysql_main_tr_party_account_role
WHERE role_code = 'PRIMARY_USER'