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

When doing the project today, the project encountered the need to group the data, the maximum value in the group, think about, do not know how to do. So on-line search, finally found the idea, after comparing this query when the fastest, in fact, there are other methods, but I think we only grasp the quickest way. All right, don't talk 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.

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvahl5odi5otaz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

What needs to be done now, take this data according to the [Customercaseno] group and remove the recent time in the Paymentstime field. To do this. The following statements are used to achieve

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 descWhen you are finished running the SQL statement in the Red Word section. Appears for example the following results


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

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