SQL to select n consecutive rows

I have a table with 2 columns defined as such:
CREATE TABLE TempConsec(SpaceID varchar(100),aislesequence int)

The data in the table may be something like this:

'space 1', 1
'space 2', 2
'space 4', 4
'space 5', 5
'space 6', 6

I have a stored procedure with a parameter of the number of consecutive spaces that I want selected.

So given a stored proc like  findConsecutive(3) what would the SQL code be to return rows 4,5,6 ?

I need the aislesequence values to be consecutive.
Who is Participating?
I just had a thought.  This should work but will likely be very slow on a large table.
-- can replace these two lines to use the passed in param
declare @num int
select @num = 3

select min(space_id)
from spaces s
-- make sure we're at the start of a set of consecutive numbers
where not exists (select 1 from spaces t where t.space_id = s.space_id - 1)
-- check if there are rows for all of the next N space_ids
and @num = (select count(*) from spaces c
    where c.space_id > s.space_id and c.space_id <= s.space_id + @num - 1)
-- make sure there is no row after that - can leave off if we want at least
-- N consecutive rows (instead of exactly N)
and not exists (select 1 from spaces u where u.space_id = s.space_id + @num)

Open in new window

you will need to ORDER BY something that makes sure its in the order you want....
then use WHERE `field` > "starting value"
-- field is the name of the controling field --> essentially some way to find the first value you are
looking for and everything after it
then add LIMIT 0,3 to the end of the sql call
USe the following sample, this 'll help u..
DECLARE @TempConsec TABLE (SpaceID varchar(100),aislesequence int)
SELECT @a = 4
INSERT INTO @TempConsec VALUES('space 1', 1)
INSERT INTO @TempConsec VALUES('space 2', 2)
INSERT INTO @TempConsec VALUES('space 4', 4)
INSERT INTO @TempConsec VALUES('space 5', 5)
INSERT INTO @TempConsec VALUES('space 6', 6)
SELECT * FROM @TempConsec WHERE aislesequence >= @a

Open in new window

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lachmannAuthor Commented:
Please read my question.

I'm not searching for values > that my parameter.

I am searching for the first consecutive sequence that is "N" rows long.

I don't know where this sequence starts in my table.
>So given a stored proc like  findConsecutive(3) what would the SQL code be to return rows 4,5,6 ?
>I need the aislesequence values to be consecutive.

so stored procedure findConsecutive(3) returns what?

lachmannAuthor Commented:
Let me try to clarify.

findConsecutive( N )

A call to this procedure would return the first set of N rows where the aislesequence values are consecutive.
If the table is defined as:

create table spaces (space varchar(50), space_id int)

then this sql should give you what you want...

select top 1 s0.space_id, s1.space_id, s2.space_id
from spaces s0, spaces s1, spaces s2
where s1.space_id = s0.space_id + 1
and s2.space_id = s1.space_id + 1
order by s0.space_id

Alternately, if you don't need the query itself to retrieve all three values, you could do:

select top 1 space_id
from spaces s
where exists (select 1 from spaces t where t.space_id = s.space_id + 1)
and exists (select 1 from spaces u where u.space_id = s.space_id + 2)
order by space_id
Hmm.  Of course, my sql only works for 3 specifically.

I don't think there is an efficient way to retrieve what you want without using dynamic sql to build a query similar to what I have above.
lachmannAuthor Commented:
Everyone is stuck on my sample from the beginning.
Forget I ever mentioned the number 3.

The parameter passed in can be anything >= 1

I need a general purpose query to find "N" consecutive values where "N" >= 1
One way to do it would be to add a third column (consecutive) to the table the is populated by triggers.

The insert trigger would:
check if inserted.space_id - 1 exists or space_id + 1 exists
if not, just set consecutive = 1 and you're done
if so, then it would need to loop over space_id and find how many it goes back/forward and populate consecutive based on that (possibly having to update other consecutives if it changed those)

The delete trigger would:
check if deleted.space_id - 1 or space_id + 1 exists
if not, all done
if both exist, "break the chain" and treat each separately
if so, loop over space_id to find how many are left being consecutive and update those

The update trigger would have to effectively do both of the above
Sigh - hit submit too soon.  Once the trigger is maintaining the consecutive column, you could just do:

select min(space_id) where consecutive = N (or >= depending if you want exactly that long or at least that long)

As I said in my previous comment, with only those columns, I don't think there is an efficient way to retrieve that.  You will have to build up the sql either in a script or a stored procedure (or use something like the trigger and third column approach I suggested).
See the attached, which assumes N = 3. To accommodate different values for N, just replace the limit clause to LIMIT 0, N and modify the having clause in the subquery to equal N - 1
Sorry, after further testing, it looks like my query also only works for N = 3. I assume by the zones indicated that you are using Sybase. Does Sybase have any analytical queries such as row_number() like Oracle or SQL Server?
Try this procedure

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
My last comment should work.  Ghunaima also submitted a procedure (though I haven't looked at it).  It would seem like the question should be closed as split between myself and ghunaima (rather than delete).
I agree with wilcoxon as then it will be available to other members requiring similar logics.
Removed a line from the previous code which I added for some testing & added the recordset against which tested my code which is working fine.

As stated in my previous comment, I think #3 - split points between http:#35706929 (wilcoxon) and http:#35711258 (ghunaima).

Wilcoxon's solution works as requested and I could't see any flaws in ghunaima's solution either.  Since the author never responded to either solution, they should be awarded the points.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.