
CREATE TABLE users (
 email VARCHAR(30),
 first_name VARCHAR(30),
 last_name VARCHAR(30),
 account_type VARCHAR(30)
);

INSERT INTO users VALUES 
 ('admin@boss.org', 'Admin', 'Boss', 'admin'),
 ('bob.carlsen@foo.bar', 'Bob', 'Carlsen', 'regular'),
 ('eddie.stevens@data.org', 'Eddie', 'Stevens', 'regular'),
 ('john.smith@xyz.org', 'John', 'Smith', 'regular'),
 ('root@boss.org', 'Root', 'Chief', 'admin')

First, let's order the records by email alphabetically, giving each an
ascending rnum value starting with 1. This will make use of the ROW_NUMBER
window function:

SELECT row_number() OVER (ORDER BY email) AS rnum,
  email, first_name, last_name, account_type
FROM users ORDER BY email;
+------+------------------------+------------+-----------+--------------+
| rnum | email                  | first_name | last_name | account_type |
+------+------------------------+------------+-----------+--------------+
|    1 | admin@boss.org         | Admin      | Boss      | admin        |
|    2 | bob.carlsen@foo.bar    | Bob        | Carlsen   | regular      |
|    3 | eddie.stevens@data.org | Eddie      | Stevens   | regular      |
|    4 | john.smith@xyz.org     | John       | Smith     | regular      |
|    5 | root@boss.org          | Root       | Chief     | admin        |
+------+------------------------+------------+-----------+--------------

We can generate separate sequences based on account type, using the PARTITION
BY clause:

SELECT row_number() OVER (PARTITION BY account_type ORDER BY email) AS rnum, 
 email, first_name, last_name, account_type
FROM users ORDER BY account_type,email;
+------+------------------------+------------+-----------+--------------+
| rnum | email                  | first_name | last_name | account_type |
+------+------------------------+------------+-----------+--------------+
|    1 | admin@boss.org         | Admin      | Boss      | admin        |
|    2 | root@boss.org          | Root       | Chief     | admin        |
|    1 | bob.carlsen@foo.bar    | Bob        | Carlsen   | regular      |
|    2 | eddie.stevens@data.org | Eddie      | Stevens   | regular      |
|    3 | john.smith@xyz.org     | John       | Smith     | regular      |
+------+------------------------+------------+-----------+--------------+

Given the following structure and data, we want to find the top 5 salaries
from each department.

CREATE TABLE employee_salaries (dept VARCHAR(20), name VARCHAR(20), salary
INT(11));

INSERT INTO employee_salaries VALUES
('Engineering', 'Dharma', 3500),
('Engineering', 'Binh', 3000),
('Engineering', 'Adalynn', 2800),
('Engineering', 'Samuel', 2500),
('Engineering', 'Cveta', 2200),
('Engineering', 'Ebele', 1800),
('Sales', 'Carbry', 500),
('Sales', 'Clytemnestra', 400),
('Sales', 'Juraj', 300),
('Sales', 'Kalpana', 300),
('Sales', 'Svantepolk', 250),
('Sales', 'Angelo', 200);

We could do this without using window functions, as follows:

select dept, name, salary
from employee_salaries as t1
where (select count(t2.salary)
   from employee_salaries as t2
   where t1.name != t2.name and
      t1.dept = t2.dept and
      t2.salary > t1.salary) < 5
order by dept, salary desc;

+-------------+--------------+--------+
| dept        | name         | salary |
+-------------+--------------+--------+
| Engineering | Dharma       |   3500 |
| Engineering | Binh         |   3000 |
| Engineering | Adalynn      |   2800 |
| Engineering | Samuel       |   2500 |
| Engineering | Cveta        |   2200 |
| Sales       | Carbry       |    500 |
| Sales       | Clytemnestra |    400 |
| Sales       | Juraj        |    300 |
| Sales       | Kalpana      |    300 |
| Sales       | Svantepolk   |    250 |
+-------------+--------------+--------+

This has a number of disadvantages:

* if there is no index, the query could take a long time if the
employee_salary_table is large
* Adding and maintaining indexes adds overhead, and even with indexes on dept
and salary, each subquery execution adds overhead by performing a lookup
through the index.

Let's try achieve the same with window functions. First, generate a rank for
all employees, using the RANK function.

select rank() over (partition by dept order by salary desc) as ranking,
  dept, name, salary
  from employee_salaries
  order by dept, ranking;
+---------+-------------+--------------+--------+
| ranking | dept        | name         | salary |
+---------+-------------+--------------+--------+
|       1 | Engineering | Dharma       |   3500 |
|       2 | Engineering | Binh         |   3000 |
|       3 | Engineering | Adalynn      |   2800 |
|       4 | Engineering | Samuel       |   2500 |
|       5 | Engineering | Cveta        |   2200 |
|       6 | Engineering | Ebele        |   1800 |
|       1 | Sales       | Carbry       |    500 |
|       2 | Sales       | Clytemnestra |    400 |
|       3 | Sales       | Juraj        |    300 |
|       3 | Sales       | Kalpana      |    300 |
|       5 | Sales       | Svantepolk   |    250 |
|       6 | Sales       | Angelo       |    200 |
+---------+-------------+--------------+--------+

Each department has a separate sequence of ranks due to the PARTITION BY
clause. This particular sequence of values for rank() is given by the ORDER BY
clause inside the window function’s OVER clause. Finally, to get our results