SQL group records the maximum value of a specified field

----------------------------------------------
-- There is a table testTable with id, name, and createDate.
-- Obtain the record (entire) with the largest createDate value in each group based on the name group.

----------------------------------------------
Create a table. The statement is as follows:

Create table [dbo]. [testTable]
( 
[Id] [int] not null identity (1, 1 ),
[Name] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[Counts] [int] NULL,
[CreateDate] [datetime] NULL
) 
GO
-- Constraints and Indexes
Alter table [dbo]. [testTable] add constraint [PK_testTable] primary key clustered ([id])
GO

 
Insert test data:

Insert into testTable (id, name, counts, createDate) values (1, 'A', 20, '01 14 2011 PM ')
Insert into testTable (id, name, counts, createDate) values (2, 'A', 10, '02 14 2011 PM ')
Insert into testTable (id, name, counts, createDate) values (3, 'B', 20, '03 14 2011 PM ')
Insert into testTable (id, name, counts, createDate) values (4, 'B', 40, '04 14 2011 PM ')
Insert into testTable (id, name, counts, createDate) values (5, 'B', 10, '05 14 2011 PM ')
Insert into testTable (id, name, counts, createDate) values (6, 'C', 20, '06 14 2011 PM ')
Insert into testTable (id, name, counts, createDate) values (7, 'C', 40, '07 14 2011 PM ')

 

Query SQL statements:

Select * from (
Select id, name, counts, createDate, row_number () over (partition by name order by createDate desc) rn
From testTable
) T where t. rn <= 1

 
The effect is as follows:

Example 2

No. Name contract date

100 zhang san 1985-5-1
100 zhang san 1986-5-1
101 Li Si 1985-5-1
101 Li Si

Find the maximum value of the contract date by grouping. The result should be

100 zhang san 1986-5-1
101 Li Si

Write the simplest and most efficient SQL query statement.

Select number, name, max (contract date)
From table
Group by number, name
Order by contract date;

-- Test data:

SQL> select * from;
 
NO NAME HT
-----------------------------------------
 
SQL> select * from;
 
NO NAME HT
-----------------------------------------
100 zhang san 1985-5-1
100 zhang san 1986-5-1
101 Li Si 1985-5-1
101 Li Si
 
SQL>
SQL> select no, name, max (ht)
2 from
3 group by no, name
4;
 
No name max (HT)
-----------------------------------------
100 zhang san 1986-5-1
101 Li Si
 
SQL> insert into a values (100, 'Zhang San', to_date ('2017-5-1 ', 'yyyy-mm-DD '));
 
1 row inserted
 
SQL> select * from;
 
NO NAME HT
-----------------------------------------
100 zhang san 1985-5-1
100 zhang san 1986-5-1
101 Li Si 1985-5-1
101 Li Si
100 zhang san
 
SQL>
SQL> select no, name, max (ht)
2 from
3 group by no, name;
 
No name max (HT)
-----------------------------------------
100 zhang san
101 Li Si

After a new piece of data is inserted, use this SQL query statement to display the data with the largest contract date just inserted.