Do a GROUP BY to a query with an ORDER BY

Your problem can be solved by a combination of GROUP BY and GROUP_CONCAT , which will allow you to group in the same row all the dates, and in the same column the other data different from the other columns using a separator.

A query similar to this:

SELECT 
    day, 
    GROUP_CONCAT(startHour ORDER BY startHour SEPARATOR '|') horasInicio, 
    GROUP_CONCAT(endHour  ORDER BY endHour SEPARATOR '|') horasFin, 
    GROUP_CONCAT(title SEPARATOR '|') titulos 

FROM ordenar_20170904

GROUP BY day
ORDER BY day,startHour;

As a result, for each row you will have your data grouped and concatenated, for example for startHour you will have your hours separated by | :

08:30:00|08:30:00|09:30:00

Then, by programming, you can do split on the separator to read each data.

Código: Ver Demo

CREATE TABLE  IF NOT EXISTS ordenar_20170904 (
                id SERIAL NOT NULL PRIMARY KEY,
                day VARCHAR(255),
                startHour TIME NOT NULL,
                endHour TIME NOT NULL,
                title VARCHAR(50)
    );



INSERT INTO ordenar_20170904 (day, startHour,endHour,title)
    VALUES 
    ('2017-08-10','08:30','13:30','Titulo 1'),
    ('2017-08-10','08:30','13:30','Titulo 2'),
    ('2017-08-10','09:30','13:30','Titulo 3'),
    ('2017-08-11','20:00','21:00','Titulo 4'),
    ('2017-08-11','19:30','21:00','Titulo 5');

SELECT 
    day, 
    GROUP_CONCAT(startHour ORDER BY startHour SEPARATOR '|') horasInicio, 
    GROUP_CONCAT(endHour  ORDER BY endHour SEPARATOR '|') horasFin, 
    GROUP_CONCAT(title SEPARATOR '|') titulos 

FROM ordenar_20170904

GROUP BY day
ORDER BY day,startHour;

Resultado:

day         horasInicio                 horasFin                    titulos
2017-08-10  08:30:00|08:30:00|09:30:00  13:30:00|13:30:00|13:30:00  Titulo 1|Titulo 2|Titulo 3
2017-08-11  19:30:00|20:00:00           21:00:00|21:00:00           Titulo 4|Titulo 5

Note: I've done SELECT of columns separately, but you can concatenate multiple columns as well.

Some observations on your table

I would like to point out that there is a serious design error in your table. All your fields of type date and / or time are declared as VARCHAR and also with a huge size, for example day is declared VARCHAR(255) . The best thing is that these fields are of type DATETIME . If in the future you need to make calculations with this field you will have serious problems, as well as possible errors that could be committed by those who manage the program when inserting the data.

The same goes for starHour and% endHour . VARCHAR(255) to store one hour? OMG :) At the most they should be declared as TIME . Moreover, you could even save the column starHour by adding it as time in the field day , because if it were DATETIME it would allow you to save a date and a time.

I hope it serves you.

Links:

• ORDER BY documentation .
• GROUP BY documentation .
• GROUP_CONCAT documentation .