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
etc........

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.
lachmannAsked:
Who is Participating?
 
wilcoxonCommented:
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

0
 
gfdosCommented:
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
0
 
waltersnowslinarnoldCommented:
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

Open in new window

0
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.
0
 
gfdosCommented:
>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?

0
 
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.
0
 
wilcoxonCommented:
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
0
 
wilcoxonCommented:
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.
0
 
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
0
 
wilcoxonCommented:
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
0
 
wilcoxonCommented:
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).
0
 
awking00Commented:
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
0
 
awking00Commented:
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?
0
 
GhunaimaCommented:
Try this procedure

 sql1.sql
0
 
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.
0
 
wilcoxonCommented:
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).
0
 
GhunaimaCommented:
I agree with wilcoxon as then it will be available to other members requiring similar logics.
0
 
GhunaimaCommented:
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
0
 
wilcoxonCommented:
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.
0
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.