MySQL Bugs: #38858: ORDERGROUP BY construct

Bug #38858	ORDERGROUP BY construct
Submitted:	18 Aug 2008 13:56	Modified:	19 Aug 2008 10:38
Reporter:	Lig Isler-Turmelle	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Triage:	Triaged: D5 (Feature request)

Description:
It would be useful to have a "ordergroup by" construct that allowed for specifying a record to represent the group, 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 the ORDERGROUP BY construct that shows the last person to join, but also enable a count of all team members, and display the result set by team id:

 select count(*) total, name "last joined", date from roster group by
 team_id order group by date desc order by team_id;

Expected Results:

 +--------+--------------+---------------------+
 | total | last joined | date |
 +--------+--------------+---------------------+
 | 5 | Jay Thompson | 2008-01-01 00:00:06 |
 | 5 | Fred Clause | 2008-01-01 00:00:06 |
 +--------+--------------+---------------------+
 

How to repeat:
na

Correction to query:

SELECT count(*) total, name "last joined", date FROM roster GROUP BY
 team_id ORDERGROUP BY date DESC ORDER BY team_id;

Please consider, our group by is not SQL Standard confirm. We already have additional features here.

[[ORDER BY [DERIVED {GROUP | ALL}] col [ ASC | DESC] [, ...]][, ...] ]

Default: ORDER BY DERIVED ALL

With SELECT .... GROUP BY ... ORDER BY DERIVED GROUP col; There will be a sorting for the rows from every column.

WITH SELECT ... ORDER BY DERIVED ALL col; there will be a sorting for the whole result. Because DERIVED ALL is default you just can use: SELECT .... ORDER BY col; here.

I already explained what I mean with DERIVED {GROUP | ALL} at bug #38859. Please also read there if you have problems to understand my short explanation here.