Cart

Ranking Window Functions

Table of Contents

SQL Ranking Functions

Standard SQL Ranking Functions assign a strict positive integer to each result row in the partition.

  1. ROW_NUMBER – first row is 1, second is 2 and so on, with no regard to the duplicate values.
  2. RANK – each group of duplicate values gets the same integer value, starting with 1. Next group gets the previous group’s value plus the length of that group.
  3. DENSE_RANK – like RANK, but next group gets the previous group’s value incremented by 1.
  4. NTILE(buckets) – divides the whole partition into buckets, labeled with 1, 2, 3 etc. First few buckets may have one more row each.

Ranking Function Popup

First time you add a ranking window function, or when you use the Edit Function Arguments contextual menu command, a highly specialized popup allows you to customize your call and eventually switch to another similar function from this category.

Here is this popup for a NTILE(4) call:

Ranking Function Popup

The popup helps you better understand what each ranking function does, through the following areas:

  1. visual sample – image that helps you understand what each selection does, with sorted sample values and specific rankings, highlighting specific use cases on duplicates.
  2. function selection – click on a radio button, or inside the left image area, or drag the selection rectangle and drop it elsewhere.
  3. function arguments – change the number of buckets for the NTILE function.
  4. function signature – the popup title is updated after each change with the new function signature.
  5. description info – friendly description on what your call does, or message is red when the specific function call is not supported.
  6. external help – the Help button brings you to this online article, for more information.

Ranking Demo Queries

The Queries > Demo Queries > Basic > Window Functions folder may already have some generated demo queries using ranking functions.

Here is the Ranking Functions demo query (click on the image to enlarge):

Ranking Functions Demo Query

Consecutive duplicate values have been highlighted in red, to better understand how RANK and DENSE_RANK in particular work on them.

The generated SQL, for SQLite (after Hide SQL for Subqueries):

SELECT name, quantity,
   ROW_NUMBER() OVER (ORDER BY quantity) AS RowNumber,
   RANK() OVER (ORDER BY quantity) AS Rank,
   DENSE_RANK() OVER (ORDER BY quantity) AS DenseRank,
   NTILE(4) OVER (ORDER BY quantity) AS NTile
FROM FruitsU
ORDER BY 2

Each OVER ORDER BY clause and the overall query’s ORDER BY use here the same data (some quantity values in ascending order, with duplicates), to better understand what the partitions are: all ranking function calls operate here on the exact same data as returned by the first quantity query column.