Apply ranking functions
May include but is not limited to: RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, NTILE
When you need to have your result sets numbered, ranked, partitioned by some custom logic, you should turn your attention towards ranking functions (also known as windowing functions). Luckily, there are only a few of them, and you can implement some good behaviors (such as paging) with the help of them. Let’s see the list:
- ROW_NUMBER: is an ideal candidate for paging data. It takes the OVER clause with an ORDER BY clause, and an optional PARTITION BY. For example: SELECT ROW_NUMBER() OVER(ORDER BY LastName) FROM Employees. When you use PARTITION BY (which must take place within the OVER clause, preceding ORDER BY), you can partition your result by the column name you’ve specified. This means that after every new value, the numbering starts again.
- RANK: the syntax is the same as was with ROW_NUMBER, but RANK, as the name shows, ranks the results by the specified ORDER BY value. When duplicates are found, it assigns the same number to the other values to them, but skips the next value.
- DENSE_RANK: the same as RANK, but without skipping any rank number.
- NTILE: divides the result set into approximate n-tiles, such as quintiles, percentiles, etc. If you have ever stumbled into statistics, you should be quite familiar with these quintiles or deciles. A good example on NTILE is to compute who are the richest in a set.
I'm a software developer professionalizing in the .NET platform and iOs development. Here you can find my notes for Microsoft certifications.
Everyone who seeks, finds
- .NET (35)
- .NET 70-536 (35)
- Configuration, Diagnostic, Management and Installation Features (6)
- Globalization, Drawing and Text Manipulation (2)
- Improving the Security of a .NET Framework Application (5)
- Interoperability, Reflection and Mailing Functionality (3)
- Serialization and IO Functionality (7)
- Service Processes, Threading and Application Domains (2)
- System Types and Collections (6)
- .NET 70-536 (35)
- ADO.NET (24)
- ASP.NET (68)
- Application Architecture (1)
- ASP.NET 70-562 (44)
- PRO ASP.NET 70-564 (20)
- Design Patterns (10)
- Objective-C (4)
- Personal (4)
- Silverlight (6)
- SQL Server (43)
- 70-433 (27)
- 70-451 (16)
- WCF (8)
- Windows Azure (8)
- WPF (20)