5

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:

  1. get all references

    SELECT DISTINCT reference FROM stackoverflow;
    
  2. for each reference

    SELECT * FROM stackoverflow WHERE reference = :reference ORDER BY time;
    
  3. 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.

  • 1
    1) MySQL, yeah? What version? 2) So you want the rows grouped by reference, and ordered by the time of the first reference in a reference grouping? 3) is there always going to be a start and stop pair for a given reference? – Philᵀᴹ Nov 13 '18 at 10:14
  • 1) MySQL client version: 5.1.61 2) sounds good. 3) at the moment yes, but in the future there can only be a start (timer is running at the moment) – louis12356 Nov 13 '18 at 10:16
  • sorting by reference id keeps entries together; job done, consider indexing the field to improve performance. if specific entries should appear in a specific order apart from the reference id, consider an additional column or table which specifies the sort order for each reference. if the reference is a value associated with hardware, this could allow a change out of h/w but keep report integrity in tact as the new reference id is given the old h/w's sort order. – Cary Reams Nov 13 '18 at 11:59
  • so, you would suggest to index each reference in a reference-group. I think it is more overhead than i need, because i already have the time - i need to order by reference, then by time (as my query already does), but it does not solve the ORDER of the reference COMBINED with the "earliest" time. I need the "reference-groups" ordered by their earliest time. – louis12356 Nov 13 '18 at 12:19
  • +1 for an excellent first question - if only all posters would include all that info! p.s. welcome to the forum! :-) – Vérace Nov 13 '18 at 13:43
4
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.

|improve this answer|||||
  • The same data is used in Andriy M's answer, but it can be realized only when MySQL version is 8+ (more ancient version knows nothing about window functions). – Akina Nov 13 '18 at 18:12
  • works like a charm. – louis12356 Nov 14 '18 at 7:38
2

Starting from version 8.0, MySQL supports window functions, including window aggregate functions. If you can afford upgrading your MySQL instance to version 8.0, you can solve your problem with a query as simple as this:

SELECT
  *
FROM
  `stackoverflow`
ORDER BY
  MIN(`time`) OVER (PARTITION BY `reference`) ASC,
  `time` ASC
;

The MIN(`time`) OVER (PARTITION BY `reference`) expression returns the smallest time value per reference for each row. In your case that will be the start time (assuming there are no inconsistencies in your data, obviously). This is equivalent to grouping rows by reference, getting MIN(time) per group and then joining back to the original table on the reference column, only in this case all those operations are effectively (though not actually) done implicitly, just by using the above mentioned expression.

Note that the values returned by the expression will only be used for sorting and not for output. In case you actually would like to return them as well, add the expression to the SELECT list:

SELECT
  *,
  MIN(`time`) OVER (PARTITION BY `reference`) AS `start_time`
FROM
  `stackoverflow`
ORDER BY
  `start_time` ASC,
  `time` ASC
;

One other note is that this solution will work with your example but not necessarily with your actual data. In your example all start times are different, so sorting only by MIN(time), time is enough. If in your actual data different reference rows can have the same start time, you will need to additionally sort by reference before sorting by time, as suggested by Akina:

SELECT
  *,
  MIN(`time`) OVER (PARTITION BY `reference`) AS `start_time`
FROM
  `stackoverflow`
ORDER BY
  `start_time` ASC,
  `reference` ASC,
  `time` ASC
;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.