MySQL Forums
Forum List  »  Newbie

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
August 19, 2009 01:44PM


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.