Difference between the four ranking functions of SQL and the four ranking functions of SQL

Source: Internet
Author: User

Difference between the four ranking functions of SQL and the four ranking functions of SQL

/* The difference between the four ranking functions: RANK () returns the ranking of each row in the partition of the result set. The row ranking is the plus one ranking before the related row. If two or more rows are associated with a ranking, each associated row gets the same ranking. DENSE_RANK () returns the row ranking in the result set partition, without any interruption in the ranking. The row ranking is equal to the number of all the rankings before the discussed row plus one. If two or more rows are subject to the ranking constraints in the same partition, each row will receive the same ranking. ROW_NUMBER () returns the serial number of an expert in the result Set Partition. The first row of each partition starts from. The order by clause determines the ORDER in which a unique ROW_NUMBER is allocated for rows in a specific partition. NTILE () distributes rows in an ordered partition to a specified number of groups. Each group is numbered from the beginning. For each row, NTILE returns the group ID of the row. */-- Create the test data table DECLARE @ TABLE table (name VARCHAR (4), score INT) insert into @ tableSELECT 'zhang 3', 129 union allselect 'Li si ', 137 union allselect 'wang 'er ', 137 union allselect 'xiaoming', 126 union allselect 'John ', 126 union allselect 'White', 125 union allselect 'black Black ', 124 union allselect 'west', 123 union allselect 'talent ', 120 union allselect 'Elder brother', 120 union allselect 'tang Dynasty ', 99 union allselect 'nima ', 99 -- check SELECT * FROM @ table SELECT name, score, ROW_NUMBER () OVER (order by score DESC) AS [ROW_NUMBER], RANK () OVER (order by score DESC) AS [RANK], DENSE_RANK () OVER (order by score DESC) AS [DENSE_RANK], NTILE (3) OVER (order by score DESC) AS [NTILE] FROM @ table

Why can't SQL ranking functions work?

Select row_number () over (order by score) as ranking from [score $]
You have missing a bracket when writing.
Rank by group function SQL

It seems that the parameter id is useless and where the ps value comes from. I hope you can understand the problem and write all the fields in the table.

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.