?
Solved

SQL to select n consecutive rows

Posted on 2011-05-06
22
Medium Priority
?
618 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:lachmann
  • 7
  • 3
  • 3
  • +4
19 Comments
 
LVL 3

Expert Comment

by:gfdos
ID: 35705892
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
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 35705904
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
 

Author Comment

by:lachmann
ID: 35705995
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 3

Expert Comment

by:gfdos
ID: 35706038
>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
 

Author Comment

by:lachmann
ID: 35706103
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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35706170
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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35706191
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
 

Author Comment

by:lachmann
ID: 35706211
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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35706239
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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 35706255
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
 
LVL 32

Expert Comment

by:awking00
ID: 35706524
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
 
LVL 32

Expert Comment

by:awking00
ID: 35706729
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
 
LVL 27

Accepted Solution

by:
wilcoxon earned 1000 total points
ID: 35706929
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
 
LVL 8

Assisted Solution

by:Ghunaima
Ghunaima earned 1000 total points
ID: 35711258
Try this procedure

 sql1.sql
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 36000524
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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 36000525
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
 
LVL 8

Expert Comment

by:Ghunaima
ID: 36001290
I agree with wilcoxon as then it will be available to other members requiring similar logics.
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 36008471
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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 36013400
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question