partition function usage in SQL Server 2005 (partition by field)

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)