Obtain the latest SQL implementation code for each category. For more information, see
Obtain the latest SQL implementation code for each category. For more information, see
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
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
The Code is as follows:
Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/--> if object_id (N 'company') is not null
Drop table company
Go
Create table company
(
Companyname varchar (2 ),
Product varchar (60)
)
-- Company 1
Insert into company
Select 'A', 'a1' union
Select 'A', 'a2 'union
Select 'A', 'a3 'union
Select 'A', 'a4 'union
Select 'A', 'a5 'union
Select 'A', 'a6 'union
Select 'A', 'a7 'union
Select 'A', 'a8 'union
Select 'A', 'a9 'union
Select 'A', 'a10'
-- Company 2
Insert into company
Select 'B', 'b1 'union
Select 'B', 'b2' union
Select 'B', 'b3' union
Select 'B', 'b4 'union
Select 'B', 'b5 'union
Select 'B', 'b6 'union
Select 'B', 'b7 'union
Select 'B', 'b8' union
Select 'B', 'b9' union
Select 'B', 'b10'
-- Company 3
Insert into company
Select 'C', 'c1 'union
Select 'C', 'c2 'union
Select 'C', 'c3' union
Select 'C', 'c4 'union
Select 'C', 'c5 'union
Select 'C', 'c6 'union
Select 'C', 'c7' union
Select 'C', 'c8 'union
Select 'C', 'c9 'union
Select 'C', 'c10'
-- Company 4
Insert into company
Select 'D', 'd1 'union
Select 'D', 'd2 'union
Select 'D', 'd3 'union
Select 'D', 'd4 'union
Select 'D', 'd5 'union
Select 'D', 'd6 'union
Select 'D', 'd7' union
Select 'D', 'd8' union
Select 'D', 'd9' union
Select 'D', 'd10'
-- Company 5
Insert into company
Select 'E', 'e1 'union
Select 'E', 'E2' union
Select 'E', 'e3 'union
Select 'E', 'e4 'union
Select 'E', 'e5' union
Select 'E', 'e6 'union
Select 'E', 'e7 'union
Select 'E', 'e8' union
Select 'E', 'e9' union
Select 'E', 'e10'
-- Company 6
Insert into company
Select 'F', 'f1' union
Select 'F', 'F2' union
Select 'F', 'f3 'union
Select 'F', 'F4' union
Select 'F', 'f5' union
Select 'F', 'f6 'union
Select 'F', 'f7 'union
Select 'F', 'f8' union
Select 'F', 'f9' union
Select 'F', 'f1'
-- Company 7
Insert into company
Select 'G', 'g1 'union
Select 'G', 'g2 'union
Select 'G', 'g3 'union
Select 'G', 'g4 'union
Select 'G', 'g5 'union
Select 'G', 'g6' union
Select 'G', 'g7 'union
Select 'G', 'g8 'union
Select 'G', 'g9' union
Select 'G', 'g10'
-- Company 8
Insert into company
Select 'h', 'h1 'union
Select 'h', 'h2 'union
Select 'h', 'h3 'union
Select 'h', 'h4 'union
Select 'h', 'h5 'union
Select 'h', 'h6 'union
Select 'h', 'h7 'union
Select 'h', 'h8' union
Select 'h', 'h9' union
Select 'h', 'h10'
-- Company 9
Insert into company
Select 'I', 'i1' union
Select 'I', 'i2 'union
Select 'I', 'i3 'union
Select 'I', 'i4' union
Select 'I', 'i5 'union
Select 'I', 'i6' union
Select 'I', 'i7 'union
Select 'I', 'i8' union
Select 'I', 'i9' union
Select 'I', 'i10'
-- Company 10
Insert into company
Select 'J', 'j1' union
Select 'J', 'j2 'union
Select 'J', 'j3' union
Select 'J', 'j4' union
Select 'J', 'j5' union
Select 'J', 'j6' union
Select 'J', 'j7' union
Select 'J', 'j8' union
Select 'J', 'j9' union
Select 'J', 'j10'
IF (select Object_id ('tempdb .. # t') IS NULL
Select identity (int, 1, 1) as id, * into # t from company
Order by left (product, 1), cast (substring (product, 2, 2) as int)
If object_id (N 'getdata', 'P') is not null
Drop table getdata
Go
Create proc getdata
@ Num1 int -- page number
As
Begin
Select companyname, product from
(
Select row_number () over (partition by companyname order by id) as sequence number ,*
From # t
)
Where serial number = @ num1
Order by companyname
End
Go
Getdata 4
Go
DROP procedure getdata
