Oracle group obtains the first data entry

 

Group and sort multiple data and obtain the first record in each group.

 

SQL code

SELECT *

FROM (SELECT ROW_NUMBER () OVER (partition by <PK_FINANCE_ACCOUNT_1> order by <amassdate> DESC) lev,

<Hx_amass_liquid> .*

FROM

WHERE lev_= 1

Order by <PK_FINANCE_ACCOUNT_2> DESC

 

SELECT *

FROM (SELECT ROW_NUMBER () OVER (partition by <PK_FINANCE_ACCOUNT_1> order by <amassdate> DESC) lev,

<Hx_amass_liquid> .*

FROM

WHERE lev_= 1

Order by <PK_FINANCE_ACCOUNT_2> DESC

 

<PK_FINANCE_ACCOUNT_1> -- group column

 

 

 

<Amassdate> -- Sort Columns

 

 

 

<Hx_amass_liquid> -- table name

 

 

 

<PK_FINANCE_ACCOUNT_2> -- sort column

 

 

 

Note:

 

 

 

========================================================== ============================

 

 

 

When grouping, you not only want to retrieve the first record of each group, but also want to retrieve several records in each group.

 

 

SQL code

SELECT *

FROM (

 

 

SELECT ROW_NUMBER () OVER (partition by PK_FINANCE_ACCOUNT order by amassdate 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

Author: Liu Yafei