MySQL Forums
Forum List  »  Newbie

group by without subselects in MySQL 4
Posted by: Seth Ladd
Date: August 23, 2005 12:35AM

Hello,

We have a MySQL 4 database, and a legacy reporting tool. We need to, in one query, select some rows from a table using a Group By. Unfortunately, we aren't able to use subselects in this case.

Our schema looks like this:

create table events (
owner_id number,
occurred datetime,
state varchar
)

One owner may have multiple events. The data looks like this:


+-----------+-------------------------+---------------+
| owner_id | occurred | state |
+-----------+-------------------------+---------------+
| 1 | 2005-01-01 00:00:00 | BEGAN |
| 1 | 2005-01-02 00:00:00 | PURCHASED |
| 2 | 2005-01-01 00:00:00 | BEGAN |
| 2 | 2005-01-02 00:00:00 | PURCHASED |
| 2 | 2005-01-03 00:00:00 | SUBMITTED |
+-----------+-------------------------+---------------+


We need to find all events where PURCHASED is the last event. In other words, where PURCHASED events have the latest timestamp. We need this grouped by owner_id.

We're not sure how to do this without subselects. Basically, something like this:

select owner_id, max(occurred), state
from events
group by owner_id

Notice how we're not grouping by state. The above does not return the right data, but is close to intent to what we'd like.

Any tips or tricks would be most appreciated. Thanks very much in advance!
Seth

Options: ReplyQuote


Subject
Written By
Posted
group by without subselects in MySQL 4
August 23, 2005 12:35AM


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.