MySQL Forums :: Newbie :: grouping query results in their order


Advanced Search

grouping query results in their order
Posted by: David King ()
Date: August 19, 2009 01:44PM

I have a database that tracks people as they move from place to place. Some sample data would be:

| Person | Location | Time |
| P1 | L1 | 10:00 |
| P2 | L1 | 10:00 |
| P1 | L1 | 10:30 |
| P1 | L2 | 11:00 |
| P1 | L2 | 11:30 |
| P1 | L1 | 12:00 |

What I would like is a query that tell me where a specific person has been. For example for P1 the query would return:

| Location | Start_Time | End_Time |
| L1 | 10:00 | 10:30 |
| L2 | 11:00 | 11:30 |
| L1 | 12:00 | NULL (or 12:00, I don't care) |

I tried a "SELECT Location, MIN(Time) as Start_Time, MAX(Time) as End_Time FROM tbl WHERE Person="P1" GROUP BY Location" but it returns:

| Location | Start_Time | End_Time |
| L1 | 10:00 | 12:00 |
| L2 | 11:00 | 11:30 |

Which is not helpful. Basically what would be nice is if I could do an ORDER BY first and then not have the GROUP BY clause screw with the ordering but MySQL doesn't work that way. Any ideas?



Edited 1 time(s). Last edit at 08/19/2009 01:46PM by David King.

Options: ReplyQuote


Subject Written By Posted
grouping query results in their order David King 08/19/2009 01:44PM
Re: grouping query results in their order David King 08/19/2009 02:18PM
Re: grouping query results in their order David King 08/19/2009 03:07PM
Re: grouping query results in their order David King 08/19/2009 03:23PM
Re: grouping query results in their order Rick James 08/20/2009 08:29PM
Re: grouping query results in their order Jay Alverson 08/21/2009 08:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.