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 number
1SELECT
2 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 each
4 NTILE(4) OVER (
-- Highest salaries go to group 1, lowest to group 4
5 ORDER BY salary DESC
6 ) AS quartile
7FROM
8 employees
-- Only the Sales department is split
9WHERE
10 department = 'Sales'
-- Show the highest salary first
11ORDER BY
12 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

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

More SQL examples at level 5

← SQL typing practice