how 2 fill gaps
-
hi friends
I've following simple query which will return new id everytime i insert new row into a table.
select max(cast(P_colname as int))+1 AS [NewKey] from p_table
but i want fill in the gaps, not increment to the highest number.
for example i've following data
1-- task new
2-- task 1
4-- task screen
when i run abv query it returns 5 as new no
but i want no 3 before i use no 5.
how can i achieve this.
Thank you very much
-
I'm unclear what database system you're using or why
you'd use a non-numeric datatype, but something like
this would work using SQL Server. HTH.
SELECT
CASE WHEN mx = cnt THEN mx + 1
ELSE lowest END NewKey
FROM
( SELECT COUNT(*), MAX( p_columnID ),
( SELECT MIN( c1.p_columnID )
FROM p_table c1
LEFT JOIN p_table c2
ON c1.p_columnID = ( c2.p_columnID - 1 )
WHERE c2.p_columnID is null )
FROM p_table ) as a ( mx, cnt, lowest )
-
Thank u very much friend.
ur query worked perfectly.
the reason i'm using non-numeric datatype is
there is some existing data.now we decided that it should be autogenerated fld without
effecting existing data.
thank u once again.
cheers
-
i think need add 1 to lowest column i.e
lowest+1 (in case statement)otherwise its returning existing row.
cheers
-
Yes, plus one was supposed to be there! My apologies, for some unknown reason I do not see a plus sign in the reply preview window; and I was trying to figure out why/how it was not shown or whether I had made a mistake copying my answer into the message box. In cutting and pasting different alternate characters for this, I incorrectly made my final post by re-adding the + 1 verbage in the wrong place.
I meant to type this:
CASE WHEN mx = cnt THEN mx
ELSE lowest END + 1 NewKey
Glad you caught that & glad to otherwise help.
Edited by - Mongo_ks on 12/22/2003 10:52:55 PM
-
thank u very much Mongo_ks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply