ROW_NUMBER Function in SQL

Level 5189 keystrokes

ROW_NUMBER assigns sequential numbers within each partition defined by PARTITION BY.

SQL code

-- Return each active employee with their rank inside the department
1SELECT
2 name,
3 department,
4 salary,
-- ROW_NUMBER is a window function; OVER (...) defines the window
5 ROW_NUMBER() OVER (
-- Numbering restarts at 1 in each department
6 PARTITION BY department
-- The highest salary gets 1; equal salaries still get different numbers
7 ORDER BY salary DESC
-- rank_in_dept avoids the name rank, 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'
-- List each department in rank order
13ORDER BY
14 department,
15 rank_in_dept;

Output

+-------+------------+--------+--------------+
| name  | department | salary | rank_in_dept |
+-------+------------+--------+--------------+
| Carol | HR         |  65000 |            1 |
| Alice | HR         |  55000 |            2 |
| Dave  | IT         |  85000 |            1 |
| Bob   | IT         |  75000 |            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