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.
reference
, and ordered by the time of the first reference in a reference grouping? 3) is there always going to be astart
andstop
pair for a given reference? – Philᵀᴹ Nov 13 '18 at 10:14