Grouping top data is a common query in T-SQL, such as the Student information management system that takes out the top 3 students in each subject. This query is tedious to write before SQL Server 2005 and requires a temporary table association query to fetch. After SQL Server 2005, the Row_number () function was introduced, and the grouping ordering of the Row_number () function made the operation very simple.
Create DATABASE Studentdb
Go
Use Studentdb
Go
CREATE TABLE Student-Student score Table
(
ID int,--primary key
Grade int,--Class
Score INT--Fractions
)
Go
Insert Student
Select 1,1,88
UNION ALL Select 2,1,66
UNION ALL Select 3,1,75
UNION ALL Select 4,2,30
UNION ALL Select 5,2,70
UNION ALL Select 6,2,80
UNION ALL Select 7,2,60
UNION ALL Select 8,3,90
UNION ALL Select 9,3,70
UNION ALL Select 10,3,80
Go
--All student information
SELECT * FROM Student
ID Grade Score
----------- ----------- -----------
1 1 88
2 1 66
3 1 75
4 2 30
5 2 70
6 2 80
7 2 60
8 3 90
9 3 70
10 3 80
(10 rows affected)
--not class by student grade
Select *,row_number () over (order BY score Desc) as Sequence from Student
ID Grade score Sequence
----------- ----------- ----------- --------------------
8 3 90 1
1 1 88 2
6 2 80 3
10 3 80 4
3 1 75 5
9 3 70 6
5 2 70 7
2 1 66 8
7 2 60 9
4 2 30 10
(10 rows affected)
--ranked by student score after class
Select *,row_number () over (partition by Grade ORDER BY score Desc) as Sequence from Student
ID Grade score Sequence
----------- ----------- ----------- --------------------
1 1 88 1
3 1 75 2
2 1 66 3
6 2 80 1
5 2 70 2
7 2 60 3
4 2 30 4
8 3 90 1
10 3 80 2
9 3 70 3
(10 rows affected)
partition function usage in SQL Server 2005 (partition by field)