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-650ced3e8343 11:12:00 stop 05324e7b-a358-48bb-9779-08cf60038bb8
Questions
-
How do I get the rows, marked with a # into first position?
-
I think ordering by reference is wrong, but how do I keep them together? Just ordering by time would produce wrong results. It would be great, if these pairs of references could stay together.
-
Do I need a virtual table or a sub-query?
Additional information
Meanwhile I fixed this “‘SQL’ problem” in PHP with multiple separate SQL operations, as detailed below:
-
get all references
SELECT DISTINCT reference FROM stackoverflow;
-
for each reference
SELECT * FROM stackoverflow WHERE reference = :reference ORDER BY time;
-
for each row
-> generate HTML "timer"Result:
# 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 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 11c0e4ac-e7e9-418a-841f-650ced3e8343 11:12:00 stop 05324e7b-a358-48bb-9779-08cf60038bb8
The order is now as expected – it would still be great if there is a MySQL solution for this. 🙂
Note: The exact MySQL version is 5.1.61.
Best Answer
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.