CODEWITHSUNDEEP
mysqlperformancesortingeventsalternate
TomSmartBishop
TomSmartBishop

Reputation: 425

MySQL: Sort events by start time, immediatly followed by finish event

I have event data like this in my DB:

 time | obj | type
-------------------
 0012   23    start
 0014   24    start
 0023   25    start
 0034   23    end
 0056   26    start
 0058   23    start   (reusing obj 23)
 0060   25    end
 0100   24    end
 0101   23    end
 0107   26    end
 ...

And I'd like to sort it by start event time, followed by the end event of the same object:

 time | obj | type
-------------------
 0012   23    start
 0034   23    end
 0014   24    start
 0100   24    end
 0023   25    start
 0060   25    end
 0056   26    start
 0107   26    end
 0058   23    start
 0101   23    end
 ...

So far I failed to come up with an SQL query to output the data like that. Of course I could just query all start events and then after each row query for the corresponding end event from my C API, but I have multiple 10.000 end events and that seems rather inefficient.

EDIT: Sorry I forgot to mention that obj numbers can be reused (and there is an id row which is unique) when I asked the question for the first time, I updated the sample now.

Any ideas?

Upvotes: 0

Views: 118

Answers (2)

P.Salmon
P.Salmon

Reputation: 17640

if end time is always after start time this should work 

select time,obj,type
from t order by obj,time;

But you could also us a case statement in the sort

select time,obj,type
from t 
order by obj,
        case when type = 'start' then 1
        else 2
        end;

If the obj is reused then the tricky bit is allocating the id of the start type to the end type before ordering

select t1.time,t1.obj,t1.type, t1.id,
         case when t2.id is null then t1.id
         else t2.id 
         end as startid
from t t1
left join t t2 on t2.obj = t1.obj and t2.id = (select max(t.id) from t where t.obj = t1.obj and t.id < t1.id and t1.type = 'end')
order by startid,id

+------+------+-------+----+---------+
| time | obj  | type  | id | startid |
+------+------+-------+----+---------+
|   12 |   23 | start |  1 |       1 |
|   34 |   23 | end   |  4 |       1 |
|   14 |   24 | start |  2 |       2 |
|  100 |   24 | end   |  8 |       2 |
|   23 |   25 | start |  3 |       3 |
|   60 |   25 | end   |  7 |       3 |
|   56 |   26 | start |  5 |       5 |
|  107 |   26 | end   | 12 |       5 |
|   58 |   23 | start |  6 |       6 |
|  101 |   23 | end   |  9 |       6 |
|  102 |   23 | start | 10 |      10 |
|  103 |   23 | end   | 11 |      10 |
+------+------+-------+----+---------+
12 rows in set (0.00 sec)

Upvotes: 1

Nigel Ren
Nigel Ren

Reputation: 57121

You could simply rely on the ordering of the event type. As e is before s, then sort desc.

select time, obj, type
   from someTable
   order by obj, type desc

Upvotes: 1

Related Questions