PostgreSQL: find largest field value in groups of size n

find-largest-field-value-in-groups-of-size-n.sql

--create example data (20 rows)
CREATE TABLE employees (employee_id integer PRIMARY KEY, name text);
INSERT INTO employees
VALUES
    (1, 'Zachary Ashley'),
    (5, 'Oliver Zuniga'),
    (17, 'Ashley Carney'),
    (19, 'Callie Singh'),
    (23, 'Yamilet Barrett'),
    (31, 'Jamir Stanton'),
    (39, 'Monique Johns'),
    (59, 'Warren English'),
    (69, 'Charlie Mullins'),
    (71, 'Mariyah Rodriguez'),
    (88, 'Jan Massey'),
    (99, 'Cassidy Burton'),
    (110, 'Dominique Foley'),
    (115, 'Reynaldo Landry'),
    (126, 'Frederick Berry'),
    (152, 'Dakota Hooper'),
    (165, 'Giana Brady'),
    (171, 'Mathew Reilly'),
    (189, 'Charity Livingston'),
    (217, 'Israel Cannon');

--let n equal 4 and find max employee_id in every group of n rows
SELECT employee_id
FROM (
	SELECT 	employee_id,
		name,
		ROW_NUMBER() OVER (ORDER BY employee_id)
	FROM employees
) AS sq
WHERE (row_number % 4) = 0;

/* result:
 * 
 * employee_id
 * -------------
 *          19
 *          59
 *          99
 *         152
 *         217
 * (5 rows)
 */
/*
 * the above works nicely because n is a factor of the result set size but what
 * if that is not the case but we want to include the maximum value in the
 * final, smaler, group?
 */

--here n=3
 SELECT DISTINCT employee_id
 FROM (
 	SELECT 	employee_id,
 		name,
 		ROW_NUMBER() OVER (ORDER BY employee_id)
 	FROM employees
 	UNION SELECT * FROM (SELECT employee_id, name, 0 row_number FROM employees ORDER BY employee_id DESC LIMIT 1) AS ssq
 ) AS sq
 WHERE (row_number % 3) = 0
 ORDER BY employee_id;

/*
 *  employee_id
 * -------------
 *           17
 *           31
 *           69
 *           99
 *          126
 *          171
 *          217
 * (7 rows)
 */
 
 --voila