mysql select N max values per group

In this post:

• Get N numbers per group
• Get N max/min numbers per group
• Get N numbers per group additional constraints
• Table code and data

You could read also:
SQL select max and return N values
MySQL how to find highest and lowest value

Let say that you want to get N values or N max values per group in MySQL. For example get 3 persons from this table ( at the end you have creation code and data):

Our goal is to get N persons per country(in table below we take only 1 person per country):

Get N numbers per group

In order to get N numbers per group you can use special syntax in mysql using self join and count. In this example we are getting at least 2 person from country or 1 (if the country has less than N - 2):

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country 		-- controlling grouping column
AND co.id < ci.id 						-- controlling min or max 
) < 2   										-- controlling number of return per group
;

You can control the number N by changing the value: < 2 ,if you want 4 person per country you should put < 4

result:

Get N max/min numbers per group

The control of getting max or min records is done by this statement:

co.id < ci.id

so based on the above table for North Korea we will have:

• co.id < ci.id - this is getting maximum

• co.id > ci.id - this is getting minimum

So getting the minimum per group is

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id > ci.id
) < 1
;

result:

And getting the maximum per group would be:

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id
) < 1
;

result:

Get N numbers per group additional constraints

If you want to use additional constraints, for example to get only males or females this should be applied on both selects - inner and outer - otherwise you will get different result

SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id and gender = 1
) < 2
and gender = 1;

This ensures getting the only people with gender = 1. Otherwise the result final result will include the rest of possible values for gender.

Table code and data

CREATE TABLE `person` (
	`id` INT(11) NULL DEFAULT NULL,
	`country` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
	`person` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin'
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

INSERT INTO `person` (`id`, `country`, `person`) VALUES (1, 'Austria', 'Sue');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (2, 'Austria', 'Anie');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (3, 'Australia', 'John');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (4, 'Australia', 'Brian');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (5, 'UK', 'Jim');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (6, 'UK', 'Tim');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (7, 'USA', 'David');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (8, 'USA', 'Mike');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (9, 'USA', 'Tom');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (10, 'N. Korea', 'Joe');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (11, 'N. Korea', 'Hue');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (12, 'N. Korea', 'Rick');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (13, 'N. Korea', 'Jamy');
INSERT INTO `person` (`id`, `country`, `person`) VALUES (14, 'Finland', 'Kimi');