how to query this

[smakawhat]

I have a basic table with the following

ID (prim key)
Fid int
Yr (4 char year)
goal float

Data looks like this:

ID Fid Yr Goal
1 1 1999 30498
2 1 1998 908707
3 1 1997 9273897
etc...
23 2 1980 23423
24 2 1979 23523
etc...

What I want to do is to have the goal for 1999 divided by the goal for 1998 as a result for each unique fid and year.

so for example year 1999 value should be .0336, 1998 should be (908707 / 9273897). When a new fid starts then it starts all over again.

I am not sure how to go about this a subquery maybe?

Any advice would be helpful thanks

 

[JarlH]

Try this:[tt]
select id, fid, yr,
goal / (select goal from tablename as t2
where t2.fid = t1.fid
and t2.yr = t1.yr -1)
from tablename as t1
[/tt]