Obtain the latest SQL implementation code for each category

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.