Posted by ZeldorBlat on 09/30/87 11:39

Erland Sommarskog wrote:
>
> SELECT a.ID, a.VId, a.Time1, a.Time2
> FROM tbl a
> JOIN tbl b ON a.VId = b.VId
> AND a.ID = b.ID +1
> WHERE a.Time1 > @Time
> AND b.Time2 < @Time
>
> Here I've taken your description by the letter. I strongly suspect
> that in real life the ids are not contiguous. But since I don't know
> what the real plot is, I did not want to do guessworks.
>
> The above query is not tested, as you did not include CREATE TABLE
> and INSERT statements.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>

I run into this all the time. You're right, Erland, that it's easy
when the keys are contiguous, but all too often they aren't. Finding
the "next" or "previous" row within a group is not something that SQL
is very good at. I found myself writing a lot of this crap:

SELECT a.ID, x.ID prevID
FROM tbl a
JOIN (SELECT a1.ID, max(a2.ID) prevID
FROM tbl a1
JOIN tbl a2 ON (a1.id > a2.id
AND a1.col1 = a2.col1
AND a1.col2 = a2.col2
AND etc....)
GROUP BY a1.ID) x ON a.ID = x.ID

All that just to find the "previous" row within the group. Needless to
say I've since moved away from this approach in favor of doing the hard
work in some other programming language.

[Back to original message]