MySQL Bugs: #38859: limiting a group

Bug #38859	limiting a group - LIMITGROUP
Submitted:	18 Aug 2008 14:17	Modified:	19 Aug 2008 9:07
Reporter:	Lig Isler-Turmelle	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	6.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It would be nice to limit the number of records in each group along with the total number of records returned, without having to twist SQL into a
pretzel to get what I need.

Table to be used in Example:
 CREATE TABLE roster (
 id INT UNSIGNED AUTO_INCREMENT NOT NULL,
 name VARCHAR(10) NOT NULL,
 role VARCHAR(10) NOT NULL,
 date DATETIME NOT NULL,
 team_id INT UNSIGNED NOT NULL,
 points INT UNSIGNED NOT NULL,
 PRIMARY KEY(id));

 INSERT INTO roster VALUES ('', 'John Black', 'coach', '2008-01-01
 00:00:01', 1, 0);
 INSERT INTO roster VALUES ('', 'Jack Black', 'coach', '2008-01-01
 00:00:02', 2, 0);

 INSERT INTO roster VALUES ('', 'Joe Smoe', 'player', '2008-01-01
 00:00:03', 1, 2);
 INSERT INTO roster VALUES ('', 'Jane Smoe', 'player', '2008-01-01
 00:00:04', 1, 3);
 INSERT INTO roster VALUES ('', 'Harry Smith', 'player', '2008-01-01
 00:00:05', 1, 1);
 INSERT INTO roster VALUES ('', 'Jay Thompson', 'player', '2008-01-01
 00:00:06', 1, 2);

 INSERT INTO roster VALUES ('', 'Philip Hass', 'player', '2008-01-01
 00:00:03', 2, 2);
 INSERT INTO roster VALUES ('', 'Julie Hass', 'player', '2008-01-01
 00:00:04', 2, 3);
 INSERT INTO roster VALUES ('', 'Janet Phillips', 'player', '2008-01-01
 00:00:05', 2, 1);
 INSERT INTO roster VALUES ('', 'Fred Clause', 'player', '2008-01-01
 00:00:06', 2, 4);
 
Example Query using LIMITGROUP that sums the points of the last 3 members entered, and only display 2 team_ids (uses construct requested at http://bugs.mysql.com/bug.php?id=38858):

 SELECT team_id, sum(points) "total points", name "last joined", date 
 FROM roster 
 GROUP BY team_id 
 ORDERGROUP BY date desc 
 LIMITGROUP 3 
 ORDER BY team_id 
 LIMIT 2;

Expected Result:

 +---------+--------------+--------------+---------------------+
 | team_id | total points | last joined | date |
 +---------+--------------+--------------+---------------------+
 | 1 | 8 | Jay Thompson | 2008-01-01 00:00:06 |
 | 2 | 6 | Fred Clause | 2008-01-01 00:00:06 |
 +---------+--------------+--------------+---------------------+
 

How to repeat:
na

More standard conform test where system don't through errors (name field was too short for data input).

CREATE TABLE roster (
 id serial NOT NULL,
 name VARCHAR(100) NOT NULL,
 role VARCHAR(10) NOT NULL,
 date DATETIME NOT NULL,
 team_id INT NOT NULL,
 points INT NOT NULL,
 PRIMARY KEY(id));

 INSERT INTO roster(name, role, date, team_id, points) VALUES ('John Black', 'coach', '2008-01-01
 00:00:01', 1, 0);
 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Jack Black', 'coach', '2008-01-01
 00:00:02', 2, 0);

 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Joe Smoe', 'player', '2008-01-01
 00:00:03', 1, 2);
 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Jane Smoe', 'player', '2008-01-01
 00:00:04', 1, 3);
 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Harry Smith', 'player', '2008-01-01
 00:00:05', 1, 1);
 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Jay Thompson', 'player', '2008-01-01
 00:00:06', 1, 2);

 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Philip Hass', 'player', '2008-01-01
 00:00:03', 2, 2);
 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Julie Hass', 'player', '2008-01-01
 00:00:04', 2, 3);
 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Janet Phillips', 'player', '2008-01-01
 00:00:05', 2, 1);
 INSERT INTO roster(name, role, date, team_id, points) VALUES ('Fred Clause', 'player', '2008-01-01
 00:00:06', 2, 4);

LIMIT is not defined at SQL Standard.

I think this feature request makes sense.

Maybe this Syntax will match better:

SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... LIMIT BY GROUP, LIMIT

I mean:
[[LIMIT [BY GROUP][DERIVED {GROUP | ALL}] number][,...]]

Default: LIMIT DERIVED ALL

This would give the possibilities:

- you can LIMIT the rows for the groups
- you can LIMIT the resulted groups
- you can LIMIT the result of all returned rows

And you can list more then one LIMIT.

Example: without limit your result will be 5 groups with 5 rows for every group.
Now you want to get only three rows from every group and only the first two groups:

LIMIT BY GROUP 3, LIMIT DERIVED GROUP 2

If you want to get a result set of 3 rows per group and not more then 15 rows output in total:

LIMIT BY GROUP 3, LIMIT DERIVED ALL 15

or because default is LIMIT DERIVED ALL you can use:

LIMIT BY GROUP 3, LIMIT 15

Bug #38857 is just another use case for this new feature.