Find missing sequence using cte

1

please suggest how to resolve this problem using cte.

Find the missing sequence. Table test_number contains the sequence for each id. Table test_number_min_max contains the minimum and maximum number for each id. We need to find the missing number between the minimum and maximum number for each id. text column can be ignored.

CREATE TABLE test_number(id NUMBER,SEQ NUMBER,text VARCHAR2(5)) ;
INSERT INTO test_number VALUES(1,1,'AA');
INSERT INTO test_number VALUES(1,3,'CC');
INSERT INTO test_number VALUES(1,4,'DD');
INSERT INTO test_number VALUES(1,5,'EE');
INSERT INTO test_number VALUES(1,6,'FF');
INSERT INTO test_number VALUES(1,7,'GG');
INSERT INTO test_number VALUES(1,8,'HH');
INSERT INTO test_number VALUES(1,10,'JJ');
INSERT INTO test_number VALUES(2,1,'KK');
INSERT INTO test_number VALUES(2,2,'LL');
INSERT INTO test_number VALUES(2,3,'MM');
INSERT INTO test_number VALUES(2,4,'NN');
INSERT INTO test_number VALUES(2,6,'PP');
INSERT INTO test_number VALUES(2,7,'QQ');
INSERT INTO test_number VALUES(3,1,'TT');
INSERT INTO test_number VALUES(3,4,'ZZ');
INSERT INTO test_number VALUES(3,5,'XX');

create tabel test_number_min_max(id number,mn number,mx number);
INSERT INTO test_number_min_max VALUES(1,1,12);
INSERT INTO test_number_min_max VALUES(2,1,9);
INSERT INTO test_number_min_max VALUES(3,1,5);

2

Looks like homework??, if so you'll need to show us how far you have got - no point us doing homework for students as they won't learn anything ...

3

this is not any kind of homework, i am just trying to learn sql though writing queries. i write following query in oracle but in sql i want to do this from cte. please suggest.

SELECT r id,rn seq FROM (SELECT ROWNUM rn FROM all_objects WHERE ROWNUM <13),
(SELECT ROWNUM r FROM all_objects
WHERE ROWNUM <4),test_number_min_max m
WHERE r=id
AND rn >= mn
AND rn <= mx
AND (r,rn) NOT IN
(SELECT id,seq FROM test_number)

4

Apologies, your question is clearer now and I now see the Oracle datatype definitions in your original

You can't use NUMBER and VARCHAR2 in MS T-SQL, I'll assume INT and VARCHAR(5)

Your approach in Oracle seems to be to check the ID against a list of pre-existing numbers to see what is missing. You can do the same in MS SQL, but there is no (infinite) list of numbers available. (It is common in MS SQL to create a "Tally" table of numbers, but that will have a finite number of rows, there are also tricks to generate numbers, which you could also do).

However, my approach would be to "Compare a row with the immediately previous row, sorted by ID, and see if there is a gap" and one way to do that is:


; WITH MyCTE
AS
(
SELECT	[RowNumber] = ROW_NUMBER()
			OVER
			(
--				PARTITION BY T_Item
				ORDER BY id, SEQ
			),
	id,
	SEQ
FROM	test_number
)
SELECT	T1.id,
	T0.SEQ,		-- Previous SEQ
	T1.SEQ 		-- This SEQ
FROM	MyCTE AS T1		-- A row (ONLY where a previous row exists)
	JOIN MyCTE AS T0	-- The previous row
		 ON T0.id = T1.id
		AND T0.RowNumber = T1.RowNumber-1
WHERE	T0.SEQ <> T1.SEQ -1	-- "Increment" is not 1

This is NOT constrained to the ranges in [test_number_min_max] - that table could just be JOINed to the query to constrain the output, however it would need some extra code if the first, or last, [SEQ] for a given [id] did not exactly match the [min] / [max] in [test_number_min_max]