Problem with a challenging algorithm

I'm stumped. I need to access the next nth row in a query loop to show version differences between posts.

I'm using <cfquery> to output the revisions by group, and this is my expected output:

Rev4
    diff(rev4.title, original.title)
    diff(rev4.brief, rev2.brief)
Rev3
    diff(rev3.body, rev2.body)
Rev2
    diff(rev2.brief, original.brief)
    diff(rev2.body, original.body)
Original
    query.title
    query.brief
    query.body

I initially thought to use Java methods to get the next query row. This does not work:

Rev4 needs to show the difference between its own brief row, and the last revision made to the brief row; which, in this case, occurred in Rev2; so it needs to jump one row.
In order to show the diff for its title row, Rev4 needs to jump to the original post since the first change to the title row occurred in Rev4 itself.

Some things to consider:

The revisions schema is one row for each edited post column; so if you load a post and edit its title and body, two records will get created in the revisions schema; one for the title and one for the body, under the same revisionGUID.
The query is grouped by revisionGUID.
It's ordered by revision date, newest to oldest; then by revision type (title, brief, body).

I tagged this with Java because ColdFusion allows us to use Java methods on queries objects, but it's not documented so merely knowing of its existence does not help me.

Anyone can show me a [better] way to do this?

The code structure I thought of:

<cfoutput query="revisions" group="revisionGUID">
    #revision.revisionGUID#
    <cfoutput>
        // conditional logic to get diff();
    <cfoutput>
</cfoutput>

Solution

Sorry that I don't know Coldfusion, but it sounds like (Java) scrollable resultSet might be useful.

From the oracle info page:

5.1 Scrolling
A result set created by executing a statement may support the ability to move backward (last-to-first) through its contents, as well as forward (first-to-last). Result sets that support this capability are called scrollable result sets. Result sets that are scrollable also support relative and absolute positioning. Absolute positioning is the ability to move directly to a row by specifying its absolute position in the result set, while relative positioning gives the ability to move to a row by specifying a position that is relative to the current row. (link: result set enhancements)

And the ResultSet api briefly mentions it at the top:

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

http://download.oracle.com/javase/1.4.2/docs/api/java/sql/ResultSet.html

Hope this helps you find what you are looking for =)