At 4:27 PM -0400 7/17/05, William Trenker wrote:
I've been searching the web on such topics like "sql calculation
between rows" but I'm not having much success. The best hint I've
found is to construct a join between the table and itself but I can't
see how to do that in a way that offsets one side of the join relative
to the other side.
Assuming there actually is a meta-data function like ROW_NUMBER(),
which is the ordinal value of the row in the query result, you can
just do something like this:
SELECT <math between 'a' cols and 'b' cols>
FROM (
SELECT ROW_NUMBER() AS myrownum, sq.*
FROM <ordered-sub-query> AS sq
) AS a FULL OUTER JOIN (
SELECT ROW_NUMBER + 1 AS myrownum, ...
FROM <ordered-sub-query> AS sq
) AS b ON b.myrownum = a.myrownum
...
So you do the join between the subquery and itself, and the "+1"
causes a single row offset in the join. Note you may have to UNION
ALL a single row to the top or bottom of each subquery in order to
prevent losing the first/last row.
-- Darren Duncan
