select missing number in sequence

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)

select missing number in sequence

Author

Topic

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-07-26 : 09:44:59

hallo .....

I am sorry, code = NVARCHAR(3)
how to make select the missing numbers from 1 to 999?
exampl.(from 1 to 10)

TabProducts
code
1
3
4
5
8
10

result to grid:
2
6
7
9

select ..... ???

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-26 : 09:58:38

[code];WITH n(n) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
a(a) AS (SELECT 0 FROM n a CROSS JOIN n b CROSS JOIN n c),
b(b) AS (SELECT 0 FROM a CROSS JOIN a b CROSS JOIN a c),
r(r) AS (SELECT ROW_NUMBER() OVER (ORDER BY b) FROM b) -- generate all numbers from 1 to 1000
SELECT * FROM r WHERE r<=1000
AND NOT EXISTS(SELECT * FROM TabProducts WHERE code=r.r)[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-26 : 10:03:54

declare @sample table(seq int)
insert @sample
select 1 union all
select 2 union all
select 5 union all
select 8

select n.number
from
(
select number from master..spt_values where type = 'p' and number between 1 and 10
) n
left join @sample s
on n.number = s.seq
where s.seq is null

No, you're never too old to Yak'n'Roll if you're too young to die.

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-07-26 : 10:08:12

r(r) AS (SELECT ROW_NUMBER() OVER (ORDER BY b) FROM b)

oups....code is NVARCHAR(3) Is it big problem?

mess:
Error converting data type nvarchar to bigint.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-27 : 09:32:39

If the code contains non-numeric characters, then yes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources