NTILE Function in SQL
Level 5135 keystrokes
NTILE(n) splits the ordered rows into n groups of nearly equal size; the first groups get the extra rows.
SQL code
-- Return each Sales employee with a quartile number1SELECT2 name,3 salary, -- NTILE(4) splits the rows into 4 groups numbered 1 to 4 -- 5 rows / 4 groups: group 1 gets 2 rows, the others 1 each4 NTILE(4) OVER ( -- Highest salaries go to group 1, lowest to group 45 ORDER BY salary DESC6 ) AS quartile7FROM8 employees-- Only the Sales department is split9WHERE10 department = 'Sales'-- Show the highest salary first11ORDER BY12 salary DESC;Output
+-------+--------+----------+ | name | salary | quartile | +-------+--------+----------+ | Carol | 95000 | 1 | | Dave | 85000 | 1 | | Alice | 75000 | 2 | | Bob | 65000 | 3 | | Eve | 55000 | 4 | +-------+--------+----------+ 5 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.
,× 2'× 2(× 2)× 2;× 1=× 1