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