| 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: | |
| Category: | MySQL Server: Parser | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[18 Aug 2008 14:06]
MySQL Verification Team
Correction to query: SELECT count(*) total, name "last joined", date FROM roster GROUP BY team_id ORDERGROUP BY date DESC ORDER BY team_id;
[19 Aug 2008 10:38]
Susanne Ebrecht
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.
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