serpentskiss Posted September 8, 2008 Share Posted September 8, 2008 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 Link to comment https://forums.phpfreaks.com/topic/123243-returning-data-from-a-single-table/ Share on other sites More sharing options...
serpentskiss Posted September 8, 2008 Author Share Posted September 8, 2008 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. Link to comment https://forums.phpfreaks.com/topic/123243-returning-data-from-a-single-table/#findComment-636496 Share on other sites More sharing options...
fenway Posted September 10, 2008 Share Posted September 10, 2008 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? Link to comment https://forums.phpfreaks.com/topic/123243-returning-data-from-a-single-table/#findComment-638330 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.