Combining every pair of rows into one MYSQL

Question

I wrote part of the query and got stuck on final part of it. Code that I have so far:

SELECT uid, 
       x, 
       y 
FROM   (SELECT uid, 
               x, 
               y, 
               ( Timediff(Max(ts), Min(ts)) ) idle_time 
        FROM   test 
        WHERE  speed = 0 
        GROUP  BY x, 
                  y, 
                  uid 
        HAVING Count(*) > 0) t 
WHERE  idle_time > 1 
ORDER  BY uid

And the resulting table is:

----------------------------------
| uId | x        | y             |  
----------------------------------
| 1   | 110      | 20            |  <-- Uid = 1 start position
----------------------------------
| 1   | 220      | 50            |  <-- Uid = 1 end position
----------------------------------
| 2   | 30      | 15             |  <-- Uid = 2 start position
----------------------------------
| 2   | 40      | 22             |  <-- Uid = 2 end position
----------------------------------

What I need to get now is:

------------------------------------------------------
| uId | x_start | x_end       | y _start    | y_end  |  
------------------------------------------------------
| 1   | 110      | 220        |  20         | 50     |
------------------------------------------------------
| 2   | 30      | 40         |  15          |  22    |
------------------------------------------------------

The results will always come in pairs. That means there can be multiple lines with the same uId, only pairs needs to be added. I know this is not difficult, any help is appreciated!!

EDIT:

Forgot to mention, that X and Y of END is not always bigger than START as in real database map coordinates is used.

how you know `{ 110 | 20 }` is the start position and not the end position?? or order doesn't matter? — Juan Carlos Oropeza, Nov 23 '17 at 18:44
Database is sorted on timestamp, so 110 | 20 is always the start position and 220 | 50 is the end. — eimisas, Nov 23 '17 at 18:46
Not really. You need something to sorted. Dataset are unorder unless you use `ORDER BY` right now you orde by uid, but both have same uId so the order is random — Juan Carlos Oropeza, Nov 23 '17 at 18:47
Please show the sample data with all the columns so that we can suggest different approach. — Harshil Doshi, Nov 23 '17 at 18:49
Thank you for observation. Let's say in this case order doesn't matter then. My main goal is every pair of lines concatenation into one now. — eimisas, Nov 23 '17 at 18:49
@Harshil Doesnt work, because you need a pair. Min/Max doesnt warranty that — Juan Carlos Oropeza, Nov 23 '17 at 18:51

Juan Carlos Oropeza · Accepted Answer · 2017-11-23T19:18:15.567

There isn't a pretty way to do it because you can't use CTE on mySQL

First you need return the timestamp on your inner query

------------------------------------------
| uId | x       | y   | ts               |
------------------------------------------
| 1   | 110     | 20  | 2017-11-23 15:01 |
------------------------------------------
| 1   | 220     | 50  | 2017-11-23 16:11 |
------------------------------------------
| 2   | 30      | 15  | 2017-11-24 05:20 |
------------------------------------------
| 2   | 40      | 22  | 2017-11-24 11:30 |
------------------------------------------

Then you can write the following query:

for simplicity I will call your innerQuery because you have to repeat the code.

SELECT q1.uid as uid
       q1.x as x_start,
       q1.y as y_start,
       q2.x as x_end,
       q2.y as y_end
FROM ( innerQuery ) as q1
JOIN ( innerQuery ) as q2
  ON q1.uid = q2.uid
 AND q1.ts < q2.ts

Combining every pair of rows into one MYSQL

1 Answers1