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 department1SELECT2 name,3 department,4 salary, -- ROW_NUMBER is a window function; OVER (...) defines the window5 ROW_NUMBER() OVER ( -- Numbering restarts at 1 in each department6 PARTITION BY department -- The highest salary gets 1; equal salaries still get different numbers7 ORDER BY salary DESC -- rank_in_dept avoids the name rank, a reserved word in MySQL 88 ) AS rank_in_dept9FROM10 employees-- WHERE runs before the window function, so only active employees are numbered11WHERE12 status = 'active'-- List each department in rank order13ORDER BY14 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. 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