Set variable to value in column

Is it possible to set a variable in a stored procedure equal to a
value from a column when that column's respective ID is equal to
max(id)-1


ID A B
1 24 24
2 53 29
3 76 47
4 32 32

What I am trying to do is update A in the last column to be equal to B
from the 4th row, plus A from the 3rd row. If I could set a variable
equal to A in row 3 (in this case 76) I could easily add the variable
to the existing value.

I've tried something like this before. I'm sure some of you will say
right away, "of coarse that doesn't work". I'm new to sql.

Set A = A + select( B from mytable where ID = (select (max(ID)-1) from
mytable))
where ID = (select (max(ID)) from mytable)

Thanks

Matt

Re: Set variable to value in column

I think you have columns and rows confused in your first paragraph. I
also can't quite figure out what you mean by variables, as A is both
(apparently) a column in the table and something you are trying to
SET.

This query might give you something to play with.

SELECT X.A +
(SELECT X
FROM YourTable as Y
WHERE Y.ID =
(select max(ID) FROM YourTable as Z
where Z.ID < A.ID))
FROM YourTable as X
WHERE A.ID =
(select max(ID) FROM YourTable)

Roy Harvey
Beacon Falls, CT

On Thu, 26 Jul 2007 20:08:43 -0000, mcolson
wrote:

>Is it possible to set a variable in a stored procedure equal to a
>value from a column when that column's respective ID is equal to
>max(id)-1
>
>
>ID A B
>1 24 24
>2 53 29
>3 76 47
>4 32 32
>
>What I am trying to do is update A in the last column to be equal to B
>from the 4th row, plus A from the 3rd row. If I could set a variable
>equal to A in row 3 (in this case 76) I could easily add the variable
>to the existing value.
>
>I've tried something like this before. I'm sure some of you will say
>right away, "of coarse that doesn't work". I'm new to sql.
>
>Set A = A + select( B from mytable where ID = (select (max(ID)-1) from
>mytable))
>where ID = (select (max(ID)) from mytable)
>
>Thanks
>
>Matt