SQL Window Functions (MariaDB), PARTITION BY

example_table.sql

DROP TABLE IF EXISTS `test`.`employee`;

CREATE TABLE `test`.`employee` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `manager_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`));
 
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Dev1', '10');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Dev2', '10');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Dev3', '11');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Dev4', '12');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Marketer1', '9');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Marketer2', '9');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('CEO', '0');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Managers Director', '7');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Marketing Director', '7');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Manager1', '8');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Manager2', '8');
INSERT INTO `test`.`employee` (`name`, `manager_id`) VALUES ('Manager3', '8');

expected_result.csv

order_by_bigger_groups_belonging_to_manager.sql

USE test;

SELECT id, name, manager_id, 
-- here we partition by manager_id which is almost the same as GROUP BY
-- but it returns all rows not grouped
COUNT(manager_id) OVER (PARTITION BY manager_id) as people_per_manager,
(SELECT emp.name FROM employee AS emp WHERE emp.id = employee.manager_id) as group_manager
FROM employee
ORDER BY people_per_manager DESC, manager_id DESC;

Links: https://mariadb.com/kb/en/window-functions-columnstore-window-functions/