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

damn sqlfiddle.com is down no easy way to covert the text formatted table into SQL statements — Raymond Nijland
– Raymond Nijland, Commented Jul 24, 2018 at 13:30

marc_s · Accepted Answer · 2018-07-26 20:17:35Z

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.

edited Jul 26, 2018 at 20:17

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 24, 2018 at 13:40

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tim Biegeleisen Over a year ago

This query doesn't generate the expected results. Ignore that it is for SQL Server (I was trying out a gaps and islands query). Look closely at the OP's data; you'll see that it makes no sense.

Raymond Nijland Over a year ago

@TimBiegeleisen your right ... this query is missing AND table1.name = table2.name AND table2.type = 'end' then it works rextester.com/TEZ58857

Tim Biegeleisen Over a year ago

OK but why only two records in the output instead of three?

Raymond Nijland Over a year ago

The expected output off the topicstarter also show two records? @TimBiegeleisen WHERE filter on name = 'xyz'.. if you didn't notice the example data has two names 'xyz' and 'zyx'

Tim Biegeleisen Over a year ago

Didn't see that +1. This is almost a gaps and islands problem from what I can tell. Good that you found a solution without having to use analytic functions.

|

Collectives™ on Stack Overflow

Merging Rows with additional row in mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related