Gets the entire record of one of the field's largest values after the SQL group

--a table with id,name,createdate TestTable
--based on the name group, get the largest record (whole) of createdate in each group.
----------------------------------------------

Create a table with the following statement:

[SQL]View Plaincopyprint?

2. CREATE TABLE [dbo].[ TestTable]
4. (
6. [ID] [int] not NULL IDENTITY (1, 1),
8. [name] [nchar] (Ten) COLLATE chinese_prc_ci_as NULL ,  
10. [Counts] [int] NULL ,  
12. [CreateDate] [DateTime] NULL   
14. )
16. GO
18. --Constraints and Indexes   
20. ALTER TABLE [dbo].[ TestTable] ADD CONSTRAINT [pk_testtable] PRIMARY KEY CLUSTERED ([id])
22. GO

Insert test data:

[SQL]View Plaincopyprint?

2. Insert into testtable (ID,name, counts,createdate) values(1, ' A ', *,' 10:52pm ')
4. Insert into testtable (ID,name, counts,createdate) values (2,' A ', ten,' 10:52pm ')
6. Insert into testtable (ID,name, counts,createdate) values(3, ' B ', +,' 10:52pm ')
8. Insert into testtable (ID,name, counts,createdate) values (4,' B ', +,' 10:52pm ')
10. Insert into testtable (ID,name, counts,createdate) values(5, ' B ', ten,' 10:52pm ')
12. Insert into testtable (ID,name, counts,createdate) values (6,' C ',,' + 10:52pm ')
14. Insert into testtable (ID,name, counts,createdate) values(7, ' C ', +-10:52pm ')

To query the SQL statement:

[SQL]View Plaincopyprint?

1. Select  *  from  (  
2. Select ID, name , Counts,createdate,row_number () over (partition by name Order by createdate desc) rn
3. from testtable
4. ) T where t.rn <=1

The results are as follows: