I am trying to help someone who posted Thread222-1530185.
I could come up with a pretty simple solution using code, and then some queries but a pure Sql solution would be better.
The OP is has a room seating database, and is searching for a a contigous sequence of seats. So if he needs 4 seats, he wants to return all seats with 4 or more available seats following it.
So the data looks like
Basically the question is given a row number and a seat number, can I build a query that gives a unique identifier to each contiguous sequence (SequenceID), and gives a number for the place in the sequence (SequenceNumber). I can do this easily in code, but can not figure out how to do this is Sql. Any thoughts? Thanks.
The sequence ID could be anything as long as it identifies all members of that sequence. The position in the sequence is also important for further queries.
I could come up with a pretty simple solution using code, and then some queries but a pure Sql solution would be better.
The OP is has a room seating database, and is searching for a a contigous sequence of seats. So if he needs 4 seats, he wants to return all seats with 4 or more available seats following it.
So the data looks like
Code:
Row seat
1 2 (beginning/end of a 1 seat sequence)
1 4 (beginning of a 7 seat seq (4-10))
1 5
1 6
1 7
1 8
1 9
1 10
2 1 (beginning of a 10 seat sequence (1-10))
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 9
2 10
Basically the question is given a row number and a seat number, can I build a query that gives a unique identifier to each contiguous sequence (SequenceID), and gives a number for the place in the sequence (SequenceNumber). I can do this easily in code, but can not figure out how to do this is Sql. Any thoughts? Thanks.
Code:
Row seat sequenceID sequenceNumber
1 2 1 1
1 4 2 1
1 5 2 2
1 6 2 3
1 7 2 4
1 8 2 5
1 9 2 6
1 10 2 7
2 1 3 1
2 2 3 2
2 3 3 3
2 4 3 4
2 5 3 5
2 6 3 6
2 7 3 7
2 8 3 8
2 9 3 9
2 10 3 10
The sequence ID could be anything as long as it identifies all members of that sequence. The position in the sequence is also important for further queries.