Impossible SQL query?

CrazyVincent

Hey, 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 one of you guys 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:

Code:

 
 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:

Code:

 
 Begin  End
 -----  ---
 B1     E3
 B2     E5
 B4     E6
 B8     E10
 B9

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.

Anyone think they can tackle this query? It can use any advanced SQL functions (subqueries, analytics, etc.) but I don't want to use PL/SQL. The output doesn't have to look exactly like that, I just need the logic. Thanks guys, and good luck!

Harry

uhm. . . . try something like this maybe?

SELECT t1.Key || t1.ID,
(select t2.Key || t2.ID from table t2 where t2.Date >= t1.date order by t2.Date limit 1)
FROM table t1
WHERE t1.Key = 'B'
ORDER BY t1.Date

Sorry about the formatting but these be primative forums.

Harry

nope nevermind . . . doesn't work.

CrazyVincent

Right... the problem with a subquery is that it will re-use end points. If you have multiple begin points followed by an end point, the first end point (grabbed via the LIMIT 1) will be the same for B1 and B2.

Now, if there was a way to use a variable to store the last-used end point, we'd be onto something. That's why I wanted to look into using the LAG function. What I wanted to do was use LAG to find the last used value, but you can't use LAG as a condition to produce another output. You would have to create a recursive function to read the LAG values... but that would require procedural logic.