Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to write a query to find N sequential items ?

Author  Topic 

laro
Starting Member

3 Posts

Posted - 2010-05-07 : 14:26:05
Hi

I have 1 table with the following 2 fields:
prodNum (primary key)
name

I need to write a query,
which find N sequential prodNum , which their name = "x".
It is impportent that the N items with name = "x", will be sequential

I don't know how to do that....
Please Help

Thnak's

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 14:36:59
something like
SELECT columns 
FROM
(SELECT *,ROW_NUMBER() OVER(ORDER BY prodNum) AS Seq
FROM Table
WHERE name='x'
)t
WHERE Seq<=N


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-09 : 17:02:04
I think OP's request close to this:

SET NOCOUNT ON;

DECLARE @T TABLE
(ProdNum int PRIMARY KEY,
Name VARCHAR(15));

DECLARE @N INT
SET @N = 5;

INSERT INTO @T
SELECT 1, 'X' UNION
SELECT 2, 'X' UNION
SELECT 3, 'X' UNION
SELECT 4, 'X' UNION
SELECT 5, 'X';

SELECT MIN(ProdNum) AS start_seq, MAX(ProdNum)AS end_seq
FROM @T
WHERE Name = 'X'
HAVING COUNT(*) = @N
AND MAX(ProdNum) - MIN(ProdNum) + 1 = @N

/*
start_seq end_seq
----------- -----------
1 5
*/
Go to Top of Page
   

- Advertisement -