RANK

Rankings per group

Module 8 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Rank books within each genre, or members by loans, ties included.

RANK() gives places; PARTITION BY starts the ranking again for each group.

Next: Count first

On your own New data, same idea

Show each member_id, their number of loans, and their place: 1 for the most loans. Members with the same number share a place.

The database

10 rows

loan_id	book_id	member_id	loan_date	returned
1	10	104	2026-01-22	2026-02-07
2	3	101	2026-02-23	2026-03-09
3	9	105	2026-03-11	2026-03-29
4	8	101	2026-03-27	2026-04-20
5	9	105	2026-04-03	2026-07-19
6	4	105	2026-04-06	2026-04-26
7	5	104	2026-04-07	2026-04-14
8	2	103	2026-04-25	2026-05-11
9	9	101	2026-07-19	2026-08-09
10	7	103	2026-10-05	NULL

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How RANK, PARTITION BY works

ROW_NUMBER() OVER (PARTITION BY genre ORDER BY year) numbers the rows 1, 2, 3 again inside each genre.

genre	title	year	position
Horror	Frankenstein	1818	1	starts again at 1
Horror	Dracula	1897	2
Horror	Carrie	1974	3
Science Fiction	Brave New World	1932	1	starts again at 1
Science Fiction	Dune	1965	2
Science Fiction	The Hunger Games	2008	3

query

SELECT genre, title, year, 
  ROW_NUMBER() OVER (PARTITION BY genre ORDER BY year) AS position
FROM books;

Every row stays: GROUP BY would squash each genre into one row instead.

What does RANK do?

RANK() OVER (ORDER BY pages DESC) gives 1 to the first row, 2 to the next... Rows that tie share a place, and the next place is skipped: 1, 2, 2, 4.
RANK, DENSE_RANK or ROW_NUMBER?

On 3, 2, 2, 1 loans: ROW_NUMBER gives 1, 2, 3, 4 (ties in no fixed order), RANK gives 1, 2, 2, 4, and DENSE_RANK gives 1, 2, 2, 3 (no gaps).
What does PARTITION BY do?

It splits the rows into groups and ranks each group on its own: PARTITION BY genre gives each genre its own number 1. Unlike GROUP BY, every row stays.
Can I rank by a total?

Yes, with GROUP BY in the same query: RANK() OVER (ORDER BY COUNT(*) DESC) ranks the groups by their count.
How is this like Excel?

RANK.EQ gives the same places as RANK: =RANK.EQ(F2, $F$2:$F$12). Ranking within a group needs COUNTIFS in Excel; in SQL it's one PARTITION BY.

Know someone fighting with a spreadsheet? Send them this page.