SQL to select n consecutive rows

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)
DECLARE @a 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

Select allOpen in new window

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?

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.

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
SQL-query.txt

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?

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.

 sql1.sql

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.