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

Source: Internet
Author: User
Tags ming

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

Related Article

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.