please help

Hi
I got a table with 2 columns as follows
col1 col2
10 193.51
10 194.5
10 202.71
20 192.79
20 197.6
20 192.9
30 192.76
30 191.91
30 187.9
Now i need to add a column dynamically thru sql statement to the table
so that my output should be as follows
here
0.511601468=(194.5/193.51-1)*100
4.221079692=(202.71/194.5-1)*100
and so on
col1 col2 col3
10 193.51 0.511601468
10 194.5 4.221079692
10 202.71 null
20 192.79 2.494942684
20 197.6 -2.37854251
20 192.9 null
30 192.76 -0.440962855
30 191.91 -2.08952113
30 187.9 Null
kalyan kameshkalikoi,
What criteria can we use to select the next row or the row that follows?
use northwind
go
create table t1 (
col1 int not null,
col2 numeric(9, 2)
)
go
insert into t1 values(10, 193.51)
insert into t1 values(10, 194.5)
insert into t1 values(10, 202.71)
insert into t1 values(20, 192.79)
insert into t1 values(20, 197.6)
insert into t1 values(20, 192.9)
insert into t1 values(30, 192.76)
insert into t1 values(30, 191.91)
insert into t1 values(30, 187.9)
go
alter table t1
add c1 int not null identity constraint uq_t1_c1 unique
go
select
a.col1,
a.col2,
(b.col2 / a.col2 - 1) * 100.00 as col3
from
t1 as a
left join
t1 as b
on b.c1 = (
select min(c.c1)
from t1 as c
where c.col1 = a.col1 and c.c1 > a.c1
)
order by a.c1
go
alter table t1
drop constraint uq_t1_c1
alter table t1
drop column c1
go
drop table t1
go
AMB
"kalikoi" wrote:

> Hi
>
> I got a table with 2 columns as follows
>
> col1 col2
>
> 10 193.51
> 10 194.5
> 10 202.71
>
> 20 192.79
> 20 197.6
> 20 192.9
>
> 30 192.76
> 30 191.91
> 30 187.9
>
> Now i need to add a column dynamically thru sql statement to the table
> so that my output should be as follows
>
> here
>
> 0.511601468=(194.5/193.51-1)*100
> 4.221079692=(202.71/194.5-1)*100
> and so on
>
> col1 col2 col3
>
> 10 193.51 0.511601468
> 10 194.5 4.221079692
> 10 202.71 null
>
> 20 192.79 2.494942684
> 20 197.6 -2.37854251
> 20 192.9 null
>
> 30 192.76 -0.440962855
> 30 191.91 -2.08952113
> 30 187.9 Null
>
> --
> kalyan kameshsql