15,937,127 members
0.00/5 (No votes)
See more:
I need result like this:

```ChNo====|=====Row Column
11     |     10
12     |     14
13     |     16
15     |     18
17     |     19
20     |     21
```

Row Column =>Will Generate Num based on ChNo Column (exists or not)

SQL
`if not exists(select * from TableNameA where ChNo = 10)`

if the given number not exists in the TableNameA(ChNo) column, i need to Auto Increment (Row Column) from 10.

The num 11 exists in ChNo Column, so i want to increment until it doesn't exists in that ChNo column.

Please give me a solution
Thanks.
Posted
Updated 12-Oct-15 5:47am
v2
Comments
Tomas Takac 7-Oct-15 3:25am
I'm confused. Are "ChNo" and "Row Column" in the same table?
Sathish km 7-Oct-15 4:36am
yes.
Tomas Takac 7-Oct-15 5:00am
How is ChNo generated? I guess this happens in your application, isn't it? Then you should generate the other number in the same place I guess.
Sathish km 7-Oct-15 5:20am
ChNo IS GENERATED BY starting input number given to textbox(UI). Only we have to the given textbox num which is already exist in table or not. If it exists means skip it and increment it to check whether it is available. Each and Every num i have to check.
Sathish km 7-Oct-15 5:20am
give any solution for this

## Solution 1

What you appear to be trying to do is "find a gap in a sequence" using SQL.

If I replicate your data as
SQL
```create table temp
(
ChNo int
)

insert into temp values (11),(12),(13),(15), (17),(20)```

The following query will return
`14<br />`
SQL
```SELECT  TOP 1 ChNo + 1
FROM    temp mo
WHERE   NOT EXISTS
(
SELECT  NULL
FROM    temp mi
WHERE   mi.ChNo = mo.ChNo + 1
)
ORDER BY
ChNo```

Adapted from the accepted answer to this post[^]
Other solutions are discussed here[^]

Comments
Maciej Los 12-Oct-15 11:48am
5ed!
Sathish km 14-Oct-15 0:15am
Sorry for no reply, Thks!
Why you are inserting to temp (11),(12),(13),(15), (17),(20)

i want to insert value into Row Column from front end,by that i/p we have to check it in sql which is available, if not, skip & increment to check next number.
CHill60 16-Oct-15 10:09am
I was recreating the test data that you presented so I could test my solution
Sathish km 17-Oct-15 0:13am
ok

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 Pete O'Hanlon 60 Richard MacCutchan 15 OriginalGriff 10 merano99 10 Member 16305744 -22
 OriginalGriff 720 Pete O'Hanlon 600 Richard Deeming 475 merano99 225 Dave Kreskowiak 180

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900