Click here to Skip to main content
15,937,127 members
Please Sign up or sign in to vote.
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)

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
Updated 12-Oct-15 5:47am
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    
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

1 solution

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

If I replicate your data as
create table temp
    ChNo int

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

The following query will return
14<br />
SELECT  TOP 1 ChNo + 1
FROM    temp mo
            SELECT  NULL
            FROM    temp mi
            WHERE   mi.ChNo = mo.ChNo + 1

Adapted from the accepted answer to this post[^]
Other solutions are discussed here[^]
Share this answer
Maciej Los 12-Oct-15 11:48am    
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    

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

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