SQL data is grouped to take the maximum value or take the first few values (sorted by a column)

When we do the project today, the project encountered the need to group data, the largest in the group, think about, do not know how to do, so the online check, and finally found the idea, after comparing this query when the fastest, in fact, there are other methods, but I think we only master the quickest way to do, good, do not say nonsense!

Go directly to the content: The following data is

SELECT [Customercaseno],[paymentstime] from [Bomsdatabase]. [dbo]. [Bal_paymentsswiftinfo] where storesno= ' zq00000034 ' GROUP by Customercaseno,[paymentstime]

Statement, this statement will be written by everyone.

Now what needs to be done, the data according to [Customercaseno] group after the last time to take out the Paymentstime field, to do this, with the following statement can be implemented

SELECT * FROM (select Storesno,[customercaseno],[paymentstime], Row_number () through (partition by Customercaseno order BY [P AYMENTSTIME] desc) as RowNum
From Bal_paymentsswiftinfo where storesno= ' zq00000034 ') ranked where Ranked.rownum <= 1 order by ranked. Customercaseno, ranked. Paymentstime desc When you finish executing the SQL statement in the Scarlet Letter section, the following results appear


To meet my requirements, after grouping the data, take out the nearest data record in the packet, and use the SQL function to Row_number () over (the partition by Group field order by sort field desc)