SQL one-to-multiple-fetch child table all fields of the latest record (Row_number () over () function application)

Row_number () over ()

Parameter 1: grouping fields PARTITION by ............ Options available

Parameter 2: Sort field ORDER BY: DESC must

Instance:

Sort by confirmdate, Payapplyid Group

SELECT * from Payapplyconfirmrecord
SELECT row_number () over (PARTITION by Payapplyid ORDER by Confirmdate DESC) rowid,* from dbo. Payapplyconfirmrecord

Results:

Practical Application Scenarios

Payment Application Form Payapply Association Audit table Payapplyconfirmrecord relationship is one-to-many, query all payment application records and the latest audit records

SELECT b.*,a.* from dbo. Payapply A
The Left JOIN (SELECT * FROM (select Row_number () over (PARTITION by Payapplyid ORDER BY confirmdate Desc) is rowid,* from dbo. Payapplyconfirmrecord) a where a.rowid=1) b on A.id=b.payapplyid

Results:

Of course, this function can also be used for deduplication and paging, which is just a data I have encountered in the development of the actual need to query

SQL one-to-multiple-fetch child table all fields of the latest record (Row_number () over () function application)