The usage of the partition function partition by and row_number () and the use of rank () are explained in detail (get the first few records in the Grouping (partition))

Source: Internet
Author: User

Reprint Address: http://www.cnblogs.com/linJie1930906722/p/6036053.html

The partition by keyword is part of the analytic function, which differs from the aggregation function in that it can return multiple records in a group, whereas aggregate functions generally have only one record that reflects the statistical value, and partition by is used to group the result set. If not specified then it takes the entire result set as a grouping, and the partition function is generally used with the rank function.

To prepare the test data:

CREATE TABLE Student  --Student score table (ID int,  --primary key Grade int,--Class score int--score) Goinsert into Student values (1,1,88) inse RT into Student values (2,1,66) insert into Student values (3,1,75) insert into Student values (4,2,30) insert INTO Student valu ES (5,2,70) insert into Student values (6,2,80) insert to Student values (7,2,60) insert into Student values (8,3,90) insert into Student values (9,3,70) insert to Student values (10,3,80) insert into Student values (11,3,80)

The usage of partition function partition by and row_number ()

1, do not class according to student performance rankings

Select *,row_number () over (order BY score Desc) as Sequence from Student

Execution Result:

2. Ranked by student score after class

Select *,row_number () over (partition by Grade ORDER BY score Desc) as Sequence from Student

Execution Result:

3. Get the first 1 (few) names of each class

SELECT * FROM (select *,row_number () over (partition by Grade ORDER BY score Desc) as Sequence from Student) T where T.sequen Ce<=1

Execution Result:

The use of the partition function partition by and ranking rank ()

1, after the class according to the student performance ranking this statement is the same score of the record of the same rank, for example: two 80 points in the 2nd place, 4th place No

Select *,rank () over (partition by Grade ORDER BY score Desc) as Sequence from Student

Execution Result:

2. Get the first 2 (several) of each class The statement is the same as the score of the same record, for example: two 80 points in the 2nd place, 4th place No

in simple terms, it is similar to row_number, except that it processes the order by field, and if the field value is the same, the line number remains unchanged.

SELECT * FROM (select *,rank () over (partition by Grade ORDER BY score Desc) as Sequence from Student) T where t.sequence< =2

Execution Result:

The use of the

partition function partition by and the usage of row_number () and the use of ranking rank () are explained in detail (get the first few records in the Grouping (partition)) (GO)

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.