A window function, found in T-SQL (used with OVER) and in DAX, that gives each row's position within its partition. In DAX it accepts ORDERBY and PARTITIONBY, skips ranks after ties unless DENSE is chosen, and returns blank for total rows.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains RANK in context, with comparison tables and the common traps.
Terms in this definition
- Window function
A function evaluated OVER a window defined by PARTITION BY, ORDER BY and optionally a frame; unlike GROUP BY aggregation it doesn't collapse rows, but adds to each one a value worked out from its related rows. Examples are RANK, ROW_NUMBER, LAG and running totals.
- T-SQL
The dialect of SQL that Microsoft uses for Azure SQL and SQL Server. Azure Monitor logs are queried with KQL instead.
- OVER
Gives a T-SQL window function its window: PARTITION BY, ORDER BY and, if wanted, a ROWS or RANGE frame. Rankings and running totals can then be worked out while every row is kept.
- DAX
Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.
- Azure AI Search partition
Storage and I/O unit of a search service, each holding part of the indexes. Adding partitions increases storage and indexing throughput; the number of search units equals replicas multiplied by partitions.
- ORDERBY
Sets how rows are sorted within each partition. This DAX helper can only appear inside window functions like OFFSET, RANK or ROWNUMBER.
- PARTITIONBY
Splits the data into groups so that ranking or row navigation starts again in each one. This DAX helper is only valid within window functions.
- BLANK
DAX uses BLANK to mean no value at all. It resembles NULL in SQL but behaves more like an empty cell in Excel, so adding 20 to it returns 20; the BLANK function creates one and ISBLANK detects it.
Related terms
- Backlog Priority
When items are dragged around a Scrum backlog, their order is saved in this hidden field. Agile and CMMI keep that order in Stack Rank.
- CONTAINSTABLE
A full-text function returning a table of matching rows, each with a
KEYand aRANKfor relevance, so results can be ranked. - FREETEXTTABLE
Gives back a KEY and a RANK for each matching row, matching as FREETEXT does; the output is then joined to the base table using its full-text key.
- Global (Org-wide default) policy
In Teams, the fallback policy for each policy type, which covers anyone who has not been given a policy of that type directly or through a group. If several could apply, a direct assignment takes precedence, followed by the group assignment with the highest rank.
- MATCHBY
Used within DAX window functions such as OFFSET, WINDOW, INDEX, RANK and ROWNUMBER, this lists the columns that pin down which row is current when the data lacks a unique key.
- msDS-PasswordSettingsPrecedence
A required attribute on every password settings object that sets its rank; when more than one object targets a user, the smallest number takes effect.
- Query Performance Insight
Draws on Query Store data in the Azure portal to rank queries in single and pooled Azure SQL databases by CPU, duration or how often they run. Without an active Query Store it has nothing to display.
- Ranking functions
The T-SQL window functions ROW_NUMBER, RANK, DENSE_RANK and NTILE, which assign numbers to rows in each partition according to the ORDER BY in their OVER clause.