CREATE TABLE table1 ([ID] [bigint] IDENTITY (1,1) not NULL, [Name] [nvarchar] (128) Not NULL, [class] int is not NULL, [date] D Atetime NOT NULL) class represents the category number. The number of categories is not fixed, there are at least thousands of categories
Date indicates when the record was updated
We now want to get the latest updated 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 Rowin Dex 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. ', ' Auto 1 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Hangzhou Daming Co., Ltd. ', ' Auto 2 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Hangzhou Daming Co., Ltd. ', ' Auto 3 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Hangzhou Daming Co., Ltd. ', ' Auto 4 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Hangzhou Daming Co., Ltd. ', ' Auto 5 ', ' 2010-7-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Beijing Small Branch Co., Ltd ', ' Car 1 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Beijing Small Branch Co., Ltd ', ' Car 2 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Beijing Small Branch Co., Ltd ', ' Car 3 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Beijing Small Branch Co., Ltd ', ' Car 4 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Shanghai Gains Co., ltd ', ' Auto 1 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Shanghai Gains Co., ltd ', ' Auto 2 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Shanghai Gains Co., ltd ', ' Auto 3 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Shanghai Gains Co., ltd ', ' Auto 4 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Tianjin Wang Wang Co., Ltd. ', ' Car 4 ', ' 2010-8-1 ')
Insert into #temp (company,product,inputdate) VALUES (' Tianjin Wang Wang Co., Ltd. ', ' car 5 ', ' 2010-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 ordinal, A.COMPANY,A.PRODUCT,A.INPUTD Ate
From #temp A
) b
Where ordinal >= @num
Order by serial number, Inputdate desc
End
Go
GetData 2
/*
Results
1 Hangzhou Daming Co., ltd 1 2010-08-01 00:00:00.000
1 Beijing Small Branch Co., Ltd. 1 2010-08-01 00:00:00.000
1 Shanghai 00:00:00.000 Co., ltd 1 2010-08-01
1 Tianjin Wang Wang Co., Ltd. 4 2010-08-01 00:00:00.000
2 Tianjin Wang Wang Co., Ltd. 5 2010-08-01 00:00:00.000
2 Shanghai 00:00:00.000 Co., ltd 2 2010-08-01
2 Beijing Small Branch Co., Ltd. 2 2010-08-01 00:00:00.000
2 Hangzhou Daming Co., ltd 2 2010-08-01 00:00:00.000
3 Hangzhou Daming Co., ltd 3 2010-08-01 00:00:00.000
3 Beijing Small Branch Co., Ltd. 3 2010-08-01 00:00:00.000
3 Shanghai 00:00:00.000 Co., ltd 3 2010-08-01
4 Beijing Small Branch Co., Ltd. 4 2010-08-01 00:00:00.000
4 Beijing Small Branch Co., Ltd. 4 2010-08-01 00:00:00.000
4 Shanghai 00:00:00.000 Co., ltd 4 2010-08-01
4 Hangzhou Daming Co., ltd 4 2010-08-01 00:00:00.000
5 Hangzhou Daming Co., ltd 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 product) as ordinal, a.company,a.product,a.inputdate
From #temp A
) b
Where ordinal >= @num
Order by serial number, Inputdate desc
End
GETDATA2005 4
SELECT * FROM #temp
Select (select COUNT (*) from #temp where company+ product<=a.company+a.product) as ordinal, a.company,a.product,a.inputdate
, a.company+a.product as unique flag row
From #temp A
ORDER BY Company,product
Copy Code code as follows:
Code highlighting produced by Actipro Codehighlighter (freeware) http://www. Codehighlighter.com/-->if object_id (N ' company ') are 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 ', ' F10 '
--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--the first few pages
As
Begin
Select Companyname,product from
(
Select Row_number () over (partition by CompanyName order by ID) as ordinal number, *
From #t
) A
where ordinal = @num1
ORDER BY CompanyName
End
Go
GetData 4
Go
DROP procedure GetData
