Practice · History · Medium
Return the top 5 pharaohs by reign length: name, the length as len, and a RANK() as rnk (1 = longest). Order by rnk.
Runs in your browser. No signup needed.
pharaohs(name, reign_start, reign_end). RANK() OVER (ORDER BY (reign_end − reign_start) DESC). A window function ranks without collapsing rows.
SELECT name, (reign_end - reign_start) AS len,
RANK() OVER (ORDER BY (reign_end - reign_start) DESC) AS rnk
FROM pharaohs
ORDER BY rnk
LIMIT 5;The reference solution is checked automatically when you run your query on the practice page — results are compared row by row, and order matters for this one.
Pepi II is traditionally credited with ~94 years on the throne — possibly the longest reign in history, though some scholars argue for ~64. (Turin King List)