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 department1SELECT2 name,3 department,4 salary, -- ROW_NUMBER numbers rows 1, 2, 3... without merging rows like GROUP BY5 ROW_NUMBER() OVER ( -- Numbering restarts at 1 for each department6 PARTITION BY department -- Highest salary gets number 1; ties still get different numbers7 ORDER BY salary DESC -- The alias is not just rank, because RANK is 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'-- Show each department's rows in rank order13ORDER BY14 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. 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