SQL group fetch up-to-date data SQL Server skillfully uses row_number and partition by to group top data

After SQL Server 2005, the Row_number () function was introduced, and the grouping ordering of the Row_number () function made the operation very simple

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. Here is a simple example:

--1. Creating a Test table
CREATE TABLE #score
(
Name varchar (20),
Subject varchar (20),
Score int
)
--2. Inserting test data
Insert into #score (Name,subject,score) VALUES (' Zhang San ', ' language ', 98)
Insert into #score (Name,subject,score) VALUES (' Zhang San ', ' math ', 80)
Insert into #score (Name,subject,score) VALUES (' Zhang San ', ' English ', 90)
Insert into #score (Name,subject,score) VALUES (' John Doe ', ' language ', 88)
Insert into #score (Name,subject,score) VALUES (' John Doe ', ' math ', 86)
Insert into #score (Name,subject,score) VALUES (' John Doe ', ' English ', 88)
Insert into #score (Name,subject,score) VALUES (' Li Ming ', ' Chinese ', 60)
Insert into #score (Name,subject,score) VALUES (' Li Ming ', ' math ', 86)
Insert into #score (Name,subject,score) VALUES (' Li Ming ', ' English ', 88)
Insert into #score (Name,subject,score) VALUES (' Forest winds ', ' languages ', 74)
Insert into #score (Name,subject,score) VALUES (' Forest winds ', ' math ', 99)
Insert into #score (Name,subject,score) VALUES (' Forest winds ', ' English ', 59)
Insert into #score (Name,subject,score) VALUES (' strict ', ' English ', 96)
--3. Take the top 3 data from each discipline
SELECT * FROM
(
Select Subject,name,score,row_number () over (PARTITION by subject ORDER BY score desc) as num from #score
) T where T.num <= 3 order by subject
--4. Deleting temporary tables
TRUNCATE TABLE #score
drop table #score

Grammatical form: Row_number () Over (PARTITION by COL1 ORDER by COL2)
Explanation: Based on the COL1 grouping, the sorting is based on COL2 within the grouping, and the value computed by this function represents the sequential number of each set of internally ordered (contiguous unique within the group)

Transferred from: http://blog.csdn.net/zengcong2013/article/details/45833363

SQL group fetch up-to-date data SQL Server skillfully uses row_number and partition by to group top data