Merging Rows with additional row in mysql

Question

The output of my actual mysql query look like this:

name      type    desc         timestamp
xyz       start   desc1        2018-07-24 09:03:15
xyz       end     desc1        2018-07-24 10:31:57
xyz       start   desc2        2018-07-24 10:33:16
xyz       end     desc2        2018-07-24 10:53:27
zyx       start   desc1        2018-07-24 10:09:19
zyx       end     desc1        2018-07-24 10:24:34

I would like to merge start and end for each name and desc into one row. Something like this:

name       desc         start(timestamp)        end(timestamp)
xyz        desc1        2018-07-24 09:03:15     2018-07-24 10:31:57
xyz        desc2        2018-07-24 10:33:16     2018-07-24 10:53:27

I'm not a big expert in mysql so maybe someone can help me

Answer 1

A co-related subquery might be the easy method to get the results you need.

Remember to alias correctly really important with co-related subqueries.

SELECT 
    table1.name,
    table1.desc,
    table1.timestamp AS 'start(timestamp)', 
    (SELECT table2.timestamp
     FROM table AS table2
     WHERE table1.timestamp < table2.timestamp
       AND table1.name = table2.name
       AND table2.type = 'end'
     ORDER BY table2.timestamp ASC
     LIMIT 1) AS 'end(timestamp)'
FROM    
    table AS table1
WHERE
    table1.type = 'start'
    AND table1.name = 'xyz'
ORDER BY 
    table1.timestamp ASC

p.s to optimize this a index(name, type, timestamp) is needed.