In SQL, row_number is the first choice to obtain the Top N through Group grouping.

A product table contains four fields: id, name, city, and addtime. Because the report needs to be grouped by city, 1 million data is inserted into the table for the latest 10 products in each city, the following series of tests are performed:
Copy codeThe Code is as follows:
Create table [dbo]. [products] (
[Id] [int] IDENTITY (1, 1) not null,
[Name] [nvarchar] (50) NULL,
[Addtime] [datetime] NULL,
[City] [nvarchar] (10) NULL,
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

1. Use the row_number method to execute five times. The average speed is about 8 seconds, and the speed is the fastest.
Copy codeThe Code is as follows:
Select no, id, name, city
From (select no = row_number () over (partition by city order by addtime desc), * from products) t
Where no <11 order by city asc, addtime desc

2. I used the cross apply method and executed it three times, basically at 3 minutes 5 seconds or above, and it was very slow.
Copy codeThe Code is as follows:
Select distinct B. id, B. name, B. city from products
Cross apply (select top 10 * from products where city = a. city order by addtime desc) B

3. The Count query is used and only executed twice. The task is canceled when the first execution reaches 5 minutes. When the second execution reaches 13 minutes, the task is stopped without holding the task, it is intolerable.
Copy codeThe Code is as follows:
Select id, name, city from products
Where (select count (city) from products where a. city = city and addtime> a. addtime) <10
Order by city asc, addtime desc

4. The cursor method is used. The last test is performed five times, and every time it is completed in 10 seconds, it feels good.
Copy codeThe Code is as follows:
Declare @ city nvarchar (10)
Create table # Top (id int, name nvarchar (50), city nvarchar (10), addtime datetime)
Declare mycursor cursor
Select distinct city from products order by city asc
Open mycursor
Fetch next from mycursor into @ city
While @ fetch_status = 0
Begin
Insert into # Top
Select top 10 id, name, city, addtime from products where city = @ city
Fetch next from mycursor into @ city
End
Close mycursor
Deallocate mycursor
Select * from # Top order by city asc, addtime desc
Drop table # Top

Through the above comparison, it is not difficult to find that row_number is preferred when the Group obtains Top N, and the cursor is second. The other two are not considered. When the data volume is large, it cannot be used.