SQL - group session log entries and order the groups by session start time

I am new to SQL, and could not find out how to make below query. Surely, I am missing a quite basic expression, but I was unable to find it myself.

I have below table, used to store application log entries. Each entry has a timestamp of recording, identifier of the application that made that entry, the action the application actually performed, and a unique ID for the given session of that application instance.

item_id  item_datetime      app action  session_id
1   2011-06-20 19:01:04     a   start   23B6C531-6D38-4E47-A887-10B945B48BD9
2   2011-06-20 19:01:05     b   start   7E6D0F55-4280-46DF-A829-F6821FF028D5
3   2011-06-20 19:03:45     b   job     7E6D0F55-4280-46DF-A829-F6821FF028D5
4   2011-06-20 19:09:33     a   job     23B6C531-6D38-4E47-A887-10B945B48BD9
5   2011-06-20 19:27:00     a   quit    23B6C531-6D38-4E47-A887-10B945B48BD9
6   2011-06-20 19:50:00     c   start   12345678-1234-4321-1234-109876543210
7   2011-06-20 19:50:01     c   quit    12345678-1234-4321-1234-109876543210
8   2011-06-20 19:50:22     b   quit    7E6D0F55-4280-46DF-A829-F6821FF028D5

In this example, there are 3 different applications "a", "b" and "c" executed simultaneously, each session overlapping the others. What I need is a list groupped by sessions, and the sessions (resulted groups) should be ordered by sess开发者_开发知识库ion start time. For above example table, I expect below result:

item_id  item_datetime      app action  session_id
1   2011-06-20 19:01:04     a   start   23B6C531-6D38-4E47-A887-10B945B48BD9
4   2011-06-20 19:09:33     a   job     23B6C531-6D38-4E47-A887-10B945B48BD9
5   2011-06-20 19:27:00     a   quit    23B6C531-6D38-4E47-A887-10B945B48BD9


2   2011-06-20 19:01:05     b   start   7E6D0F55-4280-46DF-A829-F6821FF028D5
3   2011-06-20 19:03:45     b   job     7E6D0F55-4280-46DF-A829-F6821FF028D5
8   2011-06-20 19:50:22     b   quit    7E6D0F55-4280-46DF-A829-F6821FF028D5

6   2011-06-20 19:50:00     c   start   12345678-1234-4321-1234-109876543210
7   2011-06-20 19:50:01     c   quit    12345678-1234-4321-1234-109876543210

I tried group by with order by, but the problem is that the result's sorting is based on the alphabetical order of the session_id's - but this is not what I need, the sessions id's uniqueness is what matters, their "value" does not matter.

You could use a subquery to find the first date a session was seen. That allows you to order sessions:

select  yt.item_id
,       yt.item_datetime
,       yt.app
,       yt.action
,       yt.session_id
from    (
        select  session_id
        ,       min(item_datetime) as MinDate
        from    YourTable
        group by
                session_id
        ) sessions
left join
        YourTable yt
on      yt.session_id = sessions.session_id
order by
        session.MinDate
,       yt.item_datetime

It appears to me that you do not want grouping but only sorting no? So :

SELECT item_id,item_datetime,app,action,session_id
FROM Table
ORDER BY app,item_datetime

Just add the clause

ORDER BY `session_id` ASC , `item_datetime` ASC

to your SQL qyery

Have you tried anything like this?

SELECT
  *
FROM
  yourTable
ORDER BY
  app,
  session_id,
  item_datetime

It would appear to be what you have described, though you say you've tried using just an ORDER BY?