Hi I have a table with example data:
Code: +---------+--------+------------+----------+-------+ | 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 Code: 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 Code: +--------+----------+----------+ | 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 edit: OK, got it down to 2 queries and a bit of PHP to merge the results: Code: 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "PHP & MySQL" group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/phpmysql?hl=en -~----------~----~----~----~------~----~------~--~---
