ROW_NUMBER Window Function in SQL

Level 5189 keystrokes

ROW_NUMBER assigns a unique sequential number to rows within each partition.

SQL code

-- Return each employee with a rank inside their department
1SELECT
2 name,
3 department,
4 salary,
-- ROW_NUMBER numbers rows 1, 2, 3... without merging rows like GROUP BY
5 ROW_NUMBER() OVER (
-- Numbering restarts at 1 for each department
6 PARTITION BY department
-- Highest salary gets number 1; ties still get different numbers
7 ORDER BY salary DESC
-- The alias is not just rank, because RANK is a reserved word in MySQL 8
8 ) AS rank_in_dept
9FROM
10 employees
-- WHERE runs before the window function, so only active employees are numbered
11WHERE
12 status = 'active'
-- Show each department's rows in rank order
13ORDER BY
14 department,
15 rank_in_dept;

Output

+-------+------------+--------+--------------+
| name  | department | salary | rank_in_dept |
+-------+------------+--------+--------------+
| Carol | HR         |  75000 |            1 |
| Alice | HR         |  65000 |            2 |
| Dan   | IT         |  90000 |            1 |
| Bob   | IT         |  85000 |            2 |
+-------+------------+--------+--------------+
4 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • _× 5
  • ,× 4
  • '× 2
  • (× 2
  • )× 2
  • ;× 1
  • =× 1

More SQL examples at level 5

← SQL typing practice