number manipulation in non-identity columns

hi.

i am using ms sql server 2000.

can somebody tell me what the code would be to remove all the values
in a given column and replace them with the associated number of the row
with each execution.

so, if i have a column:

nums
|1|
|2|
|3|
|4|

and somebody deletes record |2|

i would like the nums colum to update to

|1|
|2|
|3|

not:

|1|
|3|
|4|

it seems simple but i am having a hard time with this.
how is it done?

thanks.

SET XACT_ABORT ON
BEGIN TRANSACTION
DELETE FROM MyTable WHERE nums=@.nums
SET NOCOUNT ON
UPDATE MyTable SET nums=nums-1 WHERE nums>@.nums
SET NOCOUNT OFF
COMMIT TRANSACTION

Now I would never recommend actually doing that. It's resource intensive. You'd be better off not using a "nums" column, and using something like a CreateDate column of type datetime. Then eitehr using a subquery or SQL 2005's rank command, or a stored procedure to return a "nums" to you (In the case of using CreateDate, return back the number of other columns with a lower CreateDate), Like:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTable

Now you can go ahead and delete records and nums will adjust for you in order of CreateDate.

|||hi. thanks a lot for your reply.

i am trying to follow your last (least expensive) suggestion.
i am a little confused by the "t1" selects.

i have the following table:

PersonalPhotos
photo_id PK
photo_name
photo_location
photo_size
user_name
photo_date
photo_number

I was previouly using a stored procedure to create the non-identity number column in
photo_number.

I am now trying your code:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.CreateDate) nums
FROM PersonalPhotos

I have tried creating a t1 table with a t1_date column but that doesnt seem to work. If i replace
all the t1s with personalphoto and all the CreateDates with photo_date, i get results, but no
values in the nums column (which i could have guessed).

I am sorry, i am not a sql programmer (but i am an eager learner) and nothing is obvious.
further clarification would be appreciated.

thanks.|||i am still working on this. can somebody help clarify?
thanks|||I am pretty sure Motley meant to use a T1 table alias:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTableT1|||ok, thanks for helping out. i'll try that when i get home from work.
much appreciated.|||

hi.

i am home now and trying this. i am still confused.

as stated above i have a PersonalPhotos table with

a photo_date column that i'd like to use to delete

records and adjust numbers so they are *always* sequential.

I do not have a T1 table, but created one per Motley's suggestion.

I gave it 2 columns (t1_id, t1_date).

now, i have adjusted Motley's recommendation:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTable

to apply to the table i am working with - "PersonalPhotos":

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.t1_date) nums
FROM PersonalPhotos

When that didnt work, I tried Tmorton's suggestion:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.t1_date) nums
FROM PersonalPhotos t1

None of this works :(

The errors I am getting are all about the existence (or lack thereof) of the t1

table/columns. In the above select statement, the error I get is:

"Invalid column name 't1_date'." but, t1.t1_date clearly lives in table t1.

could somebody PLEASE explain to me:

1) what is the point of creating the new t1 table (or, do i need to manually create it)?

2) what should the columns be in the new t1 table?

3) why i am getting the Invalid column error?

clarification greatly appreciated.

|||

pbd22:

1) what is the point of creating the new t1 table (or, do i need to manually create it)?

You should NOT create a new table. Using the alias "t1" makes it possible to use the same table "PersonalPhotos" twice in the same query.

pbd22:

2) what should the columns be in the new t1 table?

There should be no new t1 table.

pbd22:

3) why i am getting the Invalid column error?

And actually, I had put the table alias against the wrong table reference.

Try your query like this; you should have better luck:
SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date
|||ok, thank you.
that makes more sense to me. i am at work now but will
try your suggestion when i get home tonight. thanks for
helping to clarify.|||Hi.

I have tried your suggestion. I am not getting the following:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.

The SQL i used was as you suggested:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

I have also tried variations on this select statement:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos t1
ORDER BY PersonalPhotos.photo_date

The above throws the following error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'PersonalPhotos' does not match with a table name or alias name used in the query.

I will keep trying (i seem to not be having much luck with this SQL) and will let you know if i stumble
on the answer. In the mean time, if you have more suggestions, I would appreciate it.

thank you.|||OK, this suggestion comes with Terri's Golden Guarantee that it will not generate an error:

SELECTPersonalPhotos.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

Note that the t1.* was replaced with a PersonalPhotos.*, because this is the table name being referred to in the FROM clause (and is what threw me off on my first reply).|||thank you terri! you rock. that one did the trick. I have one last tiny question :)
this select statement now does exactly what i want, but i need the numbers to
read 1 - N for the current user, not all users. Right now, the select is for all users
and, as a result, a user may see (9,10,11,12,13,14) next to his six pictures. My
user column is "user_name". also, how do i get the count to start at 1 (not zero)
for each user? so:

1) how do i get the count to be user-specific?
2) how do i get the count to start at 1, not zero?

thanks.|||You can use a query like this:

SELECT PersonalPhotos.*,(SELECT COUNT(*)+1 FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date AND PersonalPhotos.user_name = t1.user_name) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

I need to add that typically this sort of thing (adding row numbers) is done on the front end, where it is much less intensive. SQL Server has to work pretty hard to execute this query, as I believe it is doing an extra SELECT statement for each row in your table.|||hi. thanks. this solution now works.

i put the following code in my stored procedure (with a few changes):

BEGIN
SELECT PersonalPhotos.user_name,(SELECT COUNT(*)+1 FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date AND PersonalPhotos.user_name = t1.user_name) nums
FROM PersonalPhotosWHERE user_name = Context.User.Identity.Name.ToString()
ORDER BY PersonalPhotos.photo_dateDESC
END

i am assuming it returns a "nums" column that can be read by ASP. In the HTML,
I have the following line in my GridView control:

<asp:BoundField HeaderText="Number"DataField="nums" ReadOnly="True" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center" /
and, get the following error:

A field or property with the name 'nums' was not found on the selected data source.

how do i get the created "nums" column to appear next to the pics on the client?

thanks again.|||

thanks. i took your advice and am currently figuring this out for the client.

thanks for all your help!