Broken Sequence in Column - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Broken Sequence in Column

Author

Topic

pattonjo
Starting Member

11 Posts

Posted - 2013-06-05 : 10:55:30

I have a table that has rows by PartNo for each step (ex. 10, 20, 30, etc.) How can I query this to only return PartNos that have a BROKEN sequence (ex. Block #2 and #3 below)

PartNo StepNo
PartABC 10
PartABC 20
PartABC 30

PartDEF 10
PartDEF 20
PartDEF 40

PartGHI 10
PartGHI 30
PartGHI 60

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-05 : 11:32:27

Is the numbering always starting with ten and incrementing by ten if there are no breaks in the sequence? If so

SELECT * FROM PartsTable a
WHERE PartNo IN 
( 
	SELECT b.PartNo FROM PartsTable b
	GROUP BY b.PartNo
	HAVING COUNT(*)*10 < MAX(StepNo)
);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-08 : 03:44:40

No, it is not. What about the sequence {20, 20, 20}

Apply proper math! Or use ROW_NUMBER() function.

DECLARE	@Sample TABLE
	(
		PartNo CHAR(7) NOT NULL,
		StepNo TINYINT NOT NULL
	);

INSERT	@Sample
	(
		PartNo,
		StepNo
	)
VALUES	('PartABC', 10),
	('PartABC', 20),
	('PartABC', 30),
	('PartDEF', 10),
	('PartDEF', 20),
	('PartDEF', 40),
	('PartGHI', 20),
	('PartGHI', 20),
	('PartGHI', 20);

-- Applied Math
SELECT		PartNo
FROM		@Sample
WHERE		StepNo > 0
GROUP BY	PartNo
HAVING		NOT	(
				10 * COUNT(*) * (COUNT(*) - 1) = SUM(StepNo)
				AND MIN(StepNo) = 10
			)

-- ROW_NUMBER()
;WITH cteSource(Yak, StepNo, PartNo)
AS (
	SELECT	10 * ROW_NUMBER() OVER (PARTITION BY PartNo ORDER BY StepNo) AS Yak,
		StepNo,
		PartNo
	FROM	@Sample
)
SELECT DISTINCT PartNo
FROM		cteSource
WHERE		Yak <> StepNo

N 56°04'39.26"
E 12°55'05.63"

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-06-08 : 05:03:04

SwePerso's rownumber answer is the correct solution (nice one SwePerso).
But I couldn't wrap my head around the formula in "Applied math" sql. Ex. having partno "PartABC" with stepno 10,20,30,40 would not work, so I allowed myself editing the formula (sorry SwePerso):

quote:

Originally posted by SwePeso

No, it is not. What about the sequence {20, 20, 20}

Apply proper math! Or use ROW_NUMBER() function.

DECLARE	@Sample TABLE
	(
		PartNo CHAR(7) NOT NULL,
		StepNo TINYINT NOT NULL
	);

INSERT	@Sample
	(
		PartNo,
		StepNo
	)
VALUES	('PartABC', 10),
	('PartABC', 20),
	('PartABC', 30),
	('PartDEF', 10),
	('PartDEF', 20),
	('PartDEF', 40),
	('PartGHI', 20),
	('PartGHI', 20),
	('PartGHI', 20);

-- Applied Math
SELECT		PartNo
FROM		@Sample
WHERE		StepNo > 0
GROUP BY	PartNo
HAVING		NOT	(
				10 * COUNT(*) * (COUNT(*) - 1) (count(*)+1)*count(*)/2 = SUM(StepNo)
				AND MIN(StepNo) = 10
			)

-- ROW_NUMBER()
;WITH cteSource(Yak, StepNo, PartNo)
AS (
	SELECT	10 * ROW_NUMBER() OVER (PARTITION BY PartNo ORDER BY StepNo) AS Yak,
		StepNo,
		PartNo
	FROM	@Sample
)
SELECT DISTINCT PartNo
FROM		cteSource
WHERE		Yak <> StepNo

N 56°04'39.26"
E 12°55'05.63"

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-08 : 16:43:39

"Applied math" query still will not work on the following data:
('PartAAI', 10),
('PartAAI', 10),
('PartAAI', 40);

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-06-08 : 18:44:40

SwePerso's "row number" solution, it the most elegant. But just for the fun of it, I think this might work:


select s1.partno
  from @sample as s1
       inner join (select partno
                         ,stepno
                         ,count(*)-1 as c
                     from @sample
                    group by partno
                            ,stepno
                  ) as s2
               on s2.partno=s1.partno
 where s1.stepno>0
 group by s1.partno
 having not (10*(count(*)+1)*count(*)/2=sum(s1.stepno)
         and sum(s1.stepno%10)=0
         and sum(s2.c)=0
            )

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-08 : 21:03:05

Yes with a minor correction:

quote:

Originally posted by bitsmed

SwePerso's "row number" solution, it the most elegant. But just for the fun of it, I think this might work:


select s1.partno
  from @sample as s1
       inner join (select partno
                         ,stepno
                         ,count(*)-1 as c
                     from @sample
                    group by partno
                            ,stepno
                  ) as s2
               on s2.partno=s1.partno and s2.stepno=s1.stepno
 where s1.stepno>0
 group by s1.partno
 having not (10*(count(*)+1)*count(*)/2=sum(s1.stepno)
         and sum(s1.stepno%10)=0
         and sum(s2.c)=0
            )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources