SQL implements the latest statements for each category

In SQL usage, we may often encounter this requirement, that is, the first few pieces of information for each type are displayed. There are many implementation methods, but the efficiency issue is taken into account. Today, we use the row_number () function in SQL server2005.

The syntax of the ROW_NUMBER () function is as follows: ROW_NUMBER () OVER ([])
The partition by clause in the OVER Clause divides the result set into multiple partitions.

Order by in the OVER clause sorts ROW_NUMBER.

If you want to obtain 10 data entries of each category from the article table and sort them in reverse order, the final SQL statement is as follows:

The code is as follows:	Copy code
Select id, title, class, addtime from (select id, title, class, addtime, row_number () Over (partition by class order by id desc) as rowindex From article where status = 1) a where rowindex <11: Select id, title, class, addtime, row_number () Over (partition by class order by id desc) as rowindex From article where status = 1

First, the articles are classified by type (class) and sorted by type (rowindex ). Finally, retrieve the first 10 entries from each type. This achieves our goal. Tested, this method is highly efficient and is recommended for use.

Let's look at an example.

The code is as follows:	Copy code
Create table table1 ([ID] [bigint] IDENTITY (1,1) not null, [Name] [nvarchar] (128) not null, [class] int not null, [date] datetime not null)

Class indicates the category number. The number of categories is not fixed. There are at least thousands of categories.
Date indicates the time when the record is updated.
We want to obtain the latest 5 records for each category.

 

Solution

The code is as follows:

Copy code

Select id, name, class, date from (select id, name, class, date, row_number () over (partition by class order by date desc) as rowindex from table1) awhere rowindex <= 5 Create table # temp ( Company varchar (50 ), Product varchar (50 ), InputDate datetime ) Insert into # temp (company, product, inputDate) values ('Hangzhou Daming Co., Ltd. ', 'Vehicle 1', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Hangzhou Daming Co., Ltd. ', 'Vehicle 2', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Hangzhou Daming Co., Ltd. ', 'Vehicle 3', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Hangzhou Daming Co., Ltd. ', 'Vehicle 4', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Hangzhou Daming Co., Ltd. ', 'Vehicle 5', '2017-7-1 ') Insert into # temp (company, product, inputDate) values ('Beijing Xiaoke Co., Ltd. ', 'Vehicle 1', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Beijing Xiaoke Co., Ltd. ', 'Vehicle 2', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Beijing Xiaoke Co., Ltd. ', 'Vehicle 3', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Beijing Xiaoke Co., Ltd. ', 'Vehicle 4', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Shanghai Youde Co., Ltd. ', 'Vehicle 1', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Shanghai Youde Co., Ltd. ', 'Vehicle 2', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Shanghai Youde Co., Ltd. ', 'Vehicle 3', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Shanghai Youde Co., Ltd. ', 'Vehicle 4', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Tianjin TradeManager Ltd. ', 'Vehicle 4', '2017-8-1 ') Insert into # temp (company, product, inputDate) values ('Tianjin TradeManager Ltd. ', 'Vehicle 5', '2017-8-1 ') Select * from # temp Create proc getdata @ Num int As Begin Select top 4 * from ( Select (select count (*) from # temp where company = a. company and product <= a. product) as no., a. company, a. product, a. inputDate From # temp ) B Where SN> = @ num Order by No., inputDate desc End Go Getdata 2   /* Result 1 Hangzhou Daming Co., Ltd. automobile 1 2010-08-01 00:00:00. 000 1 Beijing Xiaoke Co., Ltd. automobile 1 2010-08-01 00:00:00. 000 1 Shanghai Youde Co., Ltd. automobile 1 2010-08-01 00:00:00. 000 1 Tianjin TradeManager Co., Ltd. automobile 4 2010-08-01 00:00:00. 000   2 Tianjin TradeManager Co., Ltd. automobile 5 2010-08-01 00:00:00. 000 2 Shanghai Youde Co., Ltd. automobile 2 2010-08-01 00:00:00. 000 2 Beijing Xiaoke Co., Ltd. automobile 2 2010-08-01 00:00:00. 000 2 Hangzhou Daming Co., Ltd. automobile 2 2010-08-01 00:00:00. 000   3 Hangzhou Daming Co., Ltd. automobile 3 2010-08-01 00:00:00. 000 3 Beijing Xiaoke Co., Ltd. automobile 3 2010-08-01 00:00:00. 000 3 Shanghai Youde Co., Ltd. automobile 3 2010-08-01 00:00:00. 000 4 Beijing Xiaoke Co., Ltd. automobile 4 2010-08-01 00:00:00. 000     4 Beijing Xiaoke Co., Ltd. automobile 4 2010-08-01 00:00:00. 000 4 Shanghai Youde Co., Ltd. automobile 4 2010-08-01 00:00:00. 000 4 Hangzhou Daming Co., Ltd. automobile 4 2010-08-01 00:00:00. 000 5 Hangzhou Daming Co., Ltd. automobile 5 2010-07-01 00:00:00. 000   */   -- Sql2005 Create proc getdata2005 @ Num int As Begin Select top 4 * from ( Select row_number () over (partition by company order by product) as sequence number, a. company, a. product, a. inputDate From # temp ) B Where SN> = @ num Order by No., inputDate desc End   Getdata2005 4 Select * from # temp Select (select count (*) from # temp where company + product <= a. company + a. product) as no., a. company, a. product, a. inputDate , A. company + a. product as uniquely identifies a row From # temp Order by company, product