SQL Server and Access data ranking (for example, score ranking)

Source: Internet
Author: User

However, before SQL server 2005, SQL SERVER 2000 did not provide this direct function for us to use. The same is true for ACCESS.

The following describes the implementation process of data ranking in two cases. The test data is as follows:

The result after ranking is as follows:


Access

Copy codeThe Code is as follows: select name, score, (select iif (isnull (sum (1), 1, sum (1) + 1) from score_rank where score>. score) as rank from score_rank a order by score desc

SqlserverCopy codeThe Code is as follows: select name, score, (select ISNULL (sum (1), 0) + 1 from score_rank where score> a. score) as rank from score_rank a order by score desc

For SQL SERVER 2005 and later versionsCopy codeThe Code is AS follows: SELECT name, score, RANK () OVER (order by score DESC) AS [rank], DENSE_RANK () OVER (order by score DESC) AS [rank1], NTILE (4) OVER (order by score DESC) AS [rank2] FROM score_rank order by score DESC;

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.