Row_ Summary of the usage of the number() function

Row_ The number() function is a type of SQLSERVER system function that assigns a continuous integer to each row in the partition of the result set. Simply put, it is to generate a unique table with the sequence number starting from the first row of each partition

Row_ The number() function is a type of SQLSERVER system function that assigns a continuous integer to each row in the partition of the result set. Simply put, it is to generate a unique table with the sequence number starting from the first row of each partition. The following is its basic syntax:

• The PartitionBY clause divides the result set into partitions. ROW_ The NUMBER () function is applied to each partition separately and the row number of each partition is reinitialized. The PartitionBY clause is optional. If not specified, ROW_ The NUMBER () function treats the entire result set as a single partition.
• The ORDER BY clause defines the logical order of rows in each partition of the result set. The ORDER BY clause is required because ROW_ The NUMBER () function is order sensitive.
• OVER() is a windowing function that performs aggregate calculation on sets

For example, using row_ The number() function sorts the courses, scores, and student numbers for the following data forms

Sno is the student ID, cno is the course studied, and score is the grade

Obtain the following syntax and query results:

You can see that the ID values are sorted by cno partition. When the cno changes, the ID will automatically start counting again, and then the grades will be arranged in descending order, so that the high and low grades of each subject and their corresponding student numbers are clear at a glance.

If there is no partitionby, row_ Number() treats the entire result set as a partition:

The value of ID will be ranked last.

When row_ When number() is used with where, row can be_ Number () is encapsulated as a sub query, and only the data from the top two of each subject is viewed as follows:

Encapsulate the entire function method and name it a, then extract the necessary conditions from a.

I also need to add row here_ A rank () and deny other than the number() function_ The rank() function, sometimes if there are two or more classmates in the first place, row_ The number() function only displays one, while using rank() and deny_ Rank() can display all of them (the difference between these two is that the former will skip sorting, while the latter will not), as shown in the figure:

This is about Row_ That's all for the article on the usage summary of the number() function, and more related Rows_ For usage content of number(), please search for previous articles of Script Home or continue browsing related articles below. We hope everyone can support Script Home more in the future!