How to get adjacent records?

Not sure if this is possible (or worthwhile to do) in pure SQL, but I'll ask anyway.

Let's say I have a bunch of numbered records in my database (extra spaces for clarity):

2 3 4     10 11 12 13     55 56 57  91     106 107

Now given a number such as "11" how could I retrieve "10, 11, 12, 13"? i.e., all the adjacent records with no gap (all numbers must be +/- 1 from each other).

Is that开发者_运维问答 possible? If so, how?

This should do the trick. @target_id is your target value (11 in the given example).

• Find the lower bound of the desired range — the largest value less than or equal to your target where value-1 doesn't exist.
• Find the upper bound of the desired range — the smallest value greater than or equal to your target where value+1 doesn't exist.
• return the rows such that lower-bound <= x <= upper-bound.

Easy!

select *
from foo t
where t.id >= ( select max(id)
                from foo x
                where x.id <= @target_id
                  and not exists ( select *
                                   from foo x1
                                   where x1.id = x.id - 1
                                 )
              )
  and t.id <= ( select min(id)
                from foo y
                where y.id >= @target_id
                  and not exists ( select *
                                   from foo y1
                                   where y1.id = y.id + 1
                                 )
              )

Index your id/sequence number column and I believe performance should be pretty good.

I can't think of anything. But if read speed is important you could consider added a field that represents the 'cluster'. For 2, 3 and 4 the cluster would be 2. For 10, 11, 12 and 13 the cluster would be 10 and so on.

The downside is that you have to update the cluster whenever you update anything. The good part is that the arithmetic probably very easy.

Without knowing the context I can't comment on whether it's worthwhile or not (I'd assume not until proven otherwise,) but anything is possible!! I'll go with pseudo-code of one method first...

Create stored procedure that takes a starting row ID, which does:

WITH t1 AS
( SELECT id, id-row_number() OVER (ORDER BY id) AS discrepancy FROM t )
SELECT id FROM t1 WHERE t1.discrepancy = 
  (SELECT discrepancy FROM t1 WHERE id=?);