Returning data from a single table

[serpentskiss]

Hi

 

I have a table with example data:

 

+---------+--------+------------+----------+-------+
| eventId | userId | date       | time     | event |
+---------+--------+------------+----------+-------+
|       1 |  10000 | 2008-02-19 | 07:13:20 |     2 |
|       2 |  10000 | 2008-02-19 | 07:19:04 |     4 |
|       3 |    171 | 2008-02-19 | 07:34:20 |     3 |
|       4 |    122 | 2008-02-19 | 07:35:40 |     3 |
|       5 |    171 | 2008-02-19 | 07:42:44 |     3 |
|       6 |    171 | 2008-02-19 | 07:42:52 |     1 |
|       7 |    171 | 2008-02-19 | 07:47:52 |     3 |
|       8 |     98 | 2008-02-19 | 07:49:04 |     4 |
|       9 |    151 | 2008-02-19 | 07:54:32 |     2 |
|      10 |     75 | 2008-02-19 | 07:57:24 |     1 |
+---------+--------+------------+----------+-------+

 

What I'm trying to return is a distinct list of userIds, the earliest time recorded for that userId, the last time recorded for that userId, and the 2 times associated events.

 

I can do it in 3 seperate queries, or just return the userId, earliest and latest times by using

SELECT DISTINCT(userId) AS userId, min(time) as start, max(time) as finish FROM table WHERE date='2008-02-19' GROUP BY userId;

 

which gives me eg

 

+--------+----------+----------+
| userId | start    | finish   |
+--------+----------+----------+
|      1 | 11:20:44 | 17:57:12 |
|      3 | 08:59:16 | 19:44:36 |
|      7 | 11:47:08 | 20:03:44 |
|      9 | 08:54:12 | 17:46:20 |
|     20 | 08:31:28 | 17:32:08 |
+--------+----------+----------+

 

However, I want to end up with

 

userId - start - start_event - finish - finish_event

 

Any ideas? I'm pulling my hair out here, and I bet it's something easy or obvious

[serpentskiss]

OK, got it down to 2 queries and a bit of PHP to merge the results:

SELECT DISTINCT(userId) AS userId, min(time) as start, event FROM table WHERE date='2008-09-05' GROUP BY userId;
SELECT DISTINCT(userId) AS userId, max(time) as finish, event FROM table WHERE date='2008-09-05' GROUP BY userId;

 

but there's got to be a way to do this in a single query. It just seems a bit, well, repetative, at the moment.

[fenway]

There's no reason you can't use min/max in the same query--- but don't you need to group by userID *AND* eventID?