Procedure for modifying the row located two positions before the last one

Question

Procedure for modifying the row located two positions before the last one

Paradise Wolf

SSC Veteran

Points: 205
More actions
September 15, 2006 at 4:00 pm

#170364

I want to create a procedure where each time I add a new record to a table I want to set the field “Display” ( BIT ), in a position that is two rows before the last one, to “false”.

How to do that ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

Does your table have identity or any column that can be used to programatically determine what you mean by "two rows before the last one"?

If so its easy to do.

Thanks

Sreejith

Paradise Wolf SSC Veteran Points: 205 More actions · Answer 2

Hi Sreejith

Yes, it has the “CartoonID” column (field ) that is set with identity starting from 1 and increasing by 1.

Paradise Wolf SSC Veteran Points: 205 More actions · Answer 3

Let me explain better.

Yes, it has the “CartoonID” column (field ) that is set with identity starting from 1 and increasing by 1.

There is a problem however, after testing addition of more records ( rows ) and then deleting them through Transact-SQL in order to make new tests, I notice that the numerical order of CartoonID is broken. For instance, I had a sequence of values: 1,2 and 3. Then I deleted the row where CartoonID = 3 through Transact-SQL and then when I tested the C# code routine that adds a new record, it broke the sequential order. Now it shows 1,2,4. So I am not sure if I can rely in the CartoonID values to determine the numerical order of the rows ( records ).

What I aim to do is this: If for example, I had 5 records with CartoonID field having sequentially values 1,2,3,4,5 with 1,2,3 set to "false" and 4,5 set to "true" in the "Display" field ( column ) then when I add a new record ( with CartoonID = 6 ), I wanted to set the "Display" value to "false" in the record with CartoonID = 4. Because I want the web page to show always the last two cartoons and hide the rest.

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 4

Hope this is what u are looking for:

Declare MinCartoonID int

select MinCartoonID = min(CartoonID) from tableName where Display = 1

Insert statements...

If (Select Count(*) from tableName where Display = 1) > 2

Begin

Update tableName set Display = 0 where CartoonID=MinCartoonID

End

Thanks

Sreejith