What is the difference between RANK and DENSE_RANK in SQLite?

Both functions assign the same number to rows that tie under the window’s ORDER BY. The difference is what comes after the tie: RANK() leaves a gap, while DENSE_RANK() does not. For example, if two rows share the highest score, those rows get rank 1 with either function. The next lower score gets rank 3 with RANK(), but rank 2 with DENSE_RANK(). In SQLite, use PARTITION BY to restart the rankings for each team. [S1]

The distinction matters when the number is meant to represent a position in a list versus a consecutive tier. A competition-style placement can have gaps; a label for consecutive score bands usually should not. These functions rank peer groups, so tied rows share a rank rather than being arbitrarily separated.

Assume a table named results with team, player, and numeric score columns. This query returns both rankings for each team:

SELECT
  team,
  player,
  score,
  RANK() OVER (
    PARTITION BY team
    ORDER BY score DESC
  ) AS rank_position,
  DENSE_RANK() OVER (
    PARTITION BY team
    ORDER BY score DESC
  ) AS dense_position
FROM results
ORDER BY team, score DESC, player;

For an invented input, suppose Team A has Mira 90, Niko 90, and Oren 80; Team B has Paz 75 and Quin 60. The result would be:

team  player  score  rank_position  dense_position
A     Mira    90     1              1
A     Niko    90     1              1
A     Oren    80     3              2
B     Paz     75     1              1
B     Quin    60     2              2

Team A’s tie creates the visible gap in rank_position; Team B starts over because of PARTITION BY team. The final ORDER BY controls display order. Its player column does not change which rows tie in the window ranking, because the window’s own ordering uses only score. [S1]

Not if the names differ. Rows are peers when all values in the window’s ORDER BY match. So ORDER BY score DESC, player makes different player names separate ordering values even when their scores match; they would no longer be tied for ranking purposes. [S1]

If equal scores should share a rank, keep the window ordering as ORDER BY score DESC. Then use a separate query-level ordering, such as ORDER BY team, score DESC, player, to choose a readable order among tied rows. The displayed sequence can be consistent without changing the ranking rule.

Choose RANK() when later positions should reflect the number of rows ahead, including the tied rows. Choose DENSE_RANK() when you want consecutive rank numbers for distinct score levels. Before adapting the example, confirm the grouping column, the score direction, and which columns define a tie. The example assumes each team’s ranking is based only on score; a different business rule may call for different window-ordering columns.

The ranking behavior shown here does not determine which meaning your application should use or establish anything about your particular table and data. Check the results against a small sample containing a tie and a second group before relying on the output.