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
 Get Missing number from query

Author  Topic 

t3cho
Starting Member

2 Posts

Posted - 2015-04-20 : 16:29:58

In database i have field pwd type varchar(3)

Values inside that field are 1, 2, 3, 4, 5, 7, 8, 9

If you take closely you will notice that 6 is missing. With this code i can found the missing one.

[code]SELECT MIN(u.pwd) + 1 AS FirstMissing
FROM users u
WHERE (u.pwd + 1) <> (SELECT TOP 1 u2.pwd
FROM users u2
WHERE u2.pwd > u.pwd)[code]

But what if there is not missing number. How can i get the highest one + 1.

I have 1, 2, 3 assuming there is no missing number my result needs to be 4.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-20 : 17:04:59
Try this:
select u1.pwd+1 as firstmissing
from users as u1
left outer join users as u2
on u2.pwd=i1.pwd+1
where u2.pwd is null
Go to Top of Page

t3cho
Starting Member

2 Posts

Posted - 2015-04-21 : 02:03:34
quote:
Originally posted by bitsmed

Try this:
select u1.pwd+1 as firstmissing
from users as u1
left outer join users as u2
on u2.pwd=i1.pwd+1
where u2.pwd is null




In this case i got two values. First one is the missing one and the second one is highest +1. But i only need one return. Also on inner join there is typing error instead of i1 go tou1.
Here you have a fidle
[url]http://sqlfiddle.com/#!6/5f9c4/3[/url] .
How to get only one record as output.

EDIT: Never mind it works perfect now. Don't waste your time here.
Thank you very much
Go to Top of Page
   

- Advertisement -