Oracle分组取第一条数据

对多数据进行分组排序后取每组第一条记录

 

SQL代码:
SELECT*   
FROM(SELECT ROW_NUMBER() OVER (PARTITION BY<PK_FINANCE_ACCOUNT_1> ORDER BY<amassdate>DESC)LEV,<hx_amass_liquid>.* FROM<hx_amass_liquid>)  
 WHERE LEV =1  
 ORDER BY<PK_FINANCE_ACCOUNT_2>DESC  

<PK_FINANCE_ACCOUNT_1> --分组列
<amassdate> -- 排序列
<hx_amass_liquid> -- 表名
<PK_FINANCE_ACCOUNT_2> -- 排序列
注：<hx_amass_liquid>.* --一定要有表名，否则会报“缺失表达式”错误

分组时不仅想取每组的第一条记录，还想取每组共有几条记录

SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY PK_FINANCE_ACCOUNT ORDER BYamassdate DESC) LEV,
            count(0) over(partition by PK_FINANCE_ACCOUNT) cnt,
            hx_amass_liquid.*
 FROM
 hx_amass_liquid
)
 WHERE LEV = 1
 ORDER BY PK_FINANCE_ACCOUNT DESC