Is there a "soft" ORDER BY / GROUP BY?

SQL-Fiddle

http://sqlfiddle.com/#!9/c82c87b/1

Table Definition

First of all, this is my table:

CREATE TABLE `stackoverflow` (
`id` VARBINARY( 36 ) NOT NULL COMMENT 'GUID generated by PHP',
`time` TIME NOT NULL COMMENT 'Current time(stamp)',
`type` VARCHAR( 10 ) NOT NULL COMMENT 'start/stop',
`reference` VARBINARY( 36 ) NOT NULL COMMENT 'multiple starts/stops to one reference',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM

Data

This is some example-data:

INSERT INTO `stackoverflow` (
`id` ,
`time` ,
`type` ,
`reference`
)
VALUES 
('03bd8e91-b9aa-4d18-be47-9e9cce903cfd', '11:00:00', 'start', '76afe924-08aa-431b-904a-66290c50da6a'), 
('ef10860a-7666-4ca0-95b6-79ef2d5b3f75', '11:01:00', 'start', 'fd064ef5-462f-489c-ae14-3cb766eb80c4'), 
('9bc72e24-a0d4-43a3-86ab-973c331e2958', '11:02:00', 'stop', 'fd064ef5-462f-489c-ae14-3cb766eb80c4'), 
('a245cda3-1196-4dba-832e-0474fd0eb0bf', '11:03:00', 'start', '05324e7b-a358-48bb-9779-08cf60038bb8'), 
('488c67e6-c21d-4356-9578-49e857259345', '11:06:00', 'stop', '76afe924-08aa-431b-904a-66290c50da6a'), 
('11c0e4ac-e7e9-418a-841f-650ced3e8343', '11:12:00', 'stop', '05324e7b-a358-48bb-9779-08cf60038bb8');

It is basically about timers - every timer (reference) has multiple starts and stops. Every entry is either a start or a stop.

Querying Data

To display these timers in HTML, I need to order the data like this:

SELECT * FROM stackoverflow ORDER BY reference DESC, time ASC;

The result looks like this:

```
ef10860a-7666-4ca0-95b6-79ef2d5b3f75 11:01:00 start fd064ef5-462f-489c-ae14-3cb766eb80c4
9bc72e24-a0d4-43a3-86ab-973c331e2958 11:02:00 stop fd064ef5-462f-489c-ae14-3cb766eb80c4
# 03bd8e91-b9aa-4d18-be47-9e9cce903cfd 11:00:00 start 76afe924-08aa-431b-904a-66290c50da6a
# 488c67e6-c21d-4356-9578-49e857259345 11:06:00 stop 76afe924-08aa-431b-904a-66290c50da6a
a245cda3-1196-4dba-832e-0474fd0eb0bf 11:03:00 start 05324e7b-a358-48bb-9779-08cf60038bb8
11c0e4ac-e7e9-418a-841f-6

SELECT s.* 
FROM stackoverflow s, ( SELECT reference, MIN(time) time
                        FROM stackoverflow
                        GROUP BY reference
                       ) o
WHERE s.reference = o.reference
ORDER BY o.time ASC, reference DESC, time ASC;

fiddle

Everything is simple. You want to sort groups by the datetime of the most aged record in the group. The subquery obtains this info, and it is used in main query by joining it to each record in a group. For to understand replace s. with in output and analyze the result.

StackExchange Database Administrators Q#222416, answer score: 4