Impossible SQL query?

Hi, I'm trying to work out this query, I've been racking my brain over this for weeks and I just can't see the solution. I'm hoping someone here will be able to see things differently than me.

I've got these data points that mark the beginning and end of a process, lets call them B (begin) and E (end.) The problem is that there is nothing that links the begin point to the end point, except a date stamp. End points always come after begin points. There could be more than one end point, in which case we take the first one and discard the rest. So we could have data like this:

ID Key Date
-- --- ------

 1   B   1/1/05
 2   B   1/2/05
 3   E   1/3/05
 4   B   1/3/05
 5   E   1/4/05
 6   E   1/4/05
 7   E   1/5/05
 8   B   1/6/05
 9   B   1/7/05
10   E   1/7/05

The result would look something like this:

Begin End
----- ---

B1     E3
B2     E5
B4     E6
B8     E10

Notice that E7 gets discarded because there are not enough begin points before it. Also notice that B9 does not have an end point. We can discard end points but never begin points.

Does anyone think they can tackle this query? It can use any advanced SQL functions (subqueries, analytics, etc.) available in Oracle 8.1.7.4, but I don't want to use PL/SQL. The output doesn't have to look exactly like that, I'm just looking for the logic. Thanks, I really appreciate any help I can get here. Or if this is impossible without PL/SQL, please let me know! Thanks! Received on Mon Oct 03 2005 - 13:43:04 CDT