After grouping SQL data, take the maximum value or the first few values (sorted by a column) and the maximum value of SQL

After grouping SQL data, take the maximum value or the first few values (sorted by a column) and the maximum value of SQL

When I was working on the project today, I thought about the maximum value in the group after grouping the data in the project. I didn't know how to do it. So I checked it online and finally found my idea, after comparing the current queries, there are actually other methods available, but I think we can only master the fastest method!

Directly Add the following content:

SELECT [CustomerCaseNo], [PaymentsTime] FROM [BOMSDatabase]. [dbo]. [BAL_paymentsSwiftInfo] where StoresNo = 'zq00000034' group by CustomerCaseNo, [PaymentsTime]

Statement.

What needs to be done now is to obtain the latest time in the PaymentsTime field after grouping the data according to [CustomerCaseNo]. To do this, use the following statement to implement

Select * from (select StoresNo, [CustomerCaseNo], [PaymentsTime], ROW_NUMBER () over (partition by CustomerCaseNo order by [PaymentsTime] desc) as rowNum
From Random where StoresNo = 'zq00000034') ranked where ranked. rowNum <= 1 order by ranked. mermercaseno, ranked. PaymentsTime desc after executing the SQL statement in the red part, the following result is displayed:


To meet my requirements, After grouping data, retrieve the latest data records in the group. The SQL function used is ROW_NUMBER () over (partition by grouping field order by sorting field desc)



SQL grouping filters the row with the largest ID

You can refer to the following statement:

Select id, name, data from haha a where id = (select max (id) from haha B where a. name = B. name)
Or
Select id, name, data from haha a where not exists (select 1 from haha B where a. name = B. name and a. id <B. id)

All columns of the row with the maximum field value in each group after the SQL query Group

Create table # a (address char (2), fenshu varchar (8 ))
Insert into # a values ('a1', 's1 ')
Insert into # a values ('a1', 's2 ')
Insert into # a values ('a2 ', 's3 ')
Insert into # a values ('a1', 's7 ')
Insert into # a values ('a2 ', 's4 ')
Insert into # a values ('a2 ', 'ss ')
Insert into # a values ('a1', 'ss ')
Insert into # a values ('a2 ', 's9 ')

In the actual syntax test, grouping conditions should be added, otherwise duplicate records will be retrieved (a. address = B. address)
Select * from #
Where fenshu in (
Select max (fenshu) from # a B where a. address = B. address
Group by address)