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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources