My brain is working and working but i still haven't found a solution for the following problem:
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
I'm working with SQL Server 2000 DB. OK, let's say I have a table with a unique integer column named Product_ID containing the following values, each one in its seperate row:
1, 3, 5, 6, 7, 8, 10, 13, 20, 21
Now I want to make a query saying for example: "Give me a sequential block of 4 numbers". That means for the above listed example data, the rows containing: 5, 6, 7, 8. Is there any solution to achive this with an SQL statement?
First, let's build our example table and populate it:
create table Products ( ProductID numeric(5) primary key ); insert into Products values (1); insert into Products values (3); insert into Products values (5); insert into Products values (6); insert into Products values (7); insert into Products values (8); insert into Products values (10); insert into Products values (13); insert into Products values (20); insert into Products values (21);
select cast(BeginProd.ProductID as varchar) + ' through ' + cast(EndProd.ProductID as varchar) "Prod Seq of 4" from Products BeginProd, Products EndProd where EndProd.ProductID - BeginProd.ProductID = 3 and ( select count(*) from Products where ProductID between BeginProd.ProductID and EndProd.ProductID ) = 4;
Prod Seq of 4 ------------- 5 through 8
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.