comparing 2 consecutive rows in a recordset

Question

Hi,

Currently,I have this objective to meet. I need to query the database for certain results. After done so, I will need to compare the records: For example: the query return me with 10 rows of records, I then need to compare: row 1 with 2, row 2 with 3, row 3 with 4 ... row 9 with 10. The final result that I wish to have is 10 or less than 10 rows of records.

I have one approach currently. I do this within a function, hand have the variables call "previous" and "current". In a loop I will always compare previous and current which I populate through the record set using a cursor.

After I got each row of filtered result, I will then input it into a physical temporary table. After all the results are in this temporary table. I'll do a query on this table and insert the result into a cursor and then returning the cursor.

The problem is: how can I not use a temporary table. I've search through online about using nested tables. but somehow I just could not get it working.

Can I have some help here on how to replace the temp table with something else? or is there other approach that I can use to compare the row columns with other rows.

Thank you!!

EDIT

Hi all,

So sorry, maybe I am not clear with my question. Here is a sample of the result that I am trying to achieve. TABLE X

Column A    B       C       D

   100      300     99      T1
   100      300     98      T2
   100      300     97      T3
   100      100     97      T4
   100      300     97      T5
   101      11      11      T6

column A is the primary key of the table. Column A has duplicates because table X is an audit table that keep tracks of all changes.column D acts as the timestamp for that record. For my query, I am only interested in changes in column A,B and D. After the query I would like to get the result as below:

Column A    B       D

   100      300     T1
   100      100     T4
   100      300     T5
   101      11      T6

Answer 1

Hi It's not very clear what exactly yuo want to accomplish. But maybe you can fetch the results of the original query in a PLSQL collection and use that to do your comparison.

Answer 2

What exactly are you doing the row comparison for? Are you looking to eliminate duplicates, or are you transforming the data into another form and then returning that?

To eliminate duplicates, look to use GROUP BY or DISTINCT functionality in your SELECT.

If you are iterating over the initial data and transforming it in some way then it is hard to do it without using a temporary table - but what exactly is your problem with the temp table? If you are concerned about the performance of a cursor then maybe you could do one outer SELECT that compares the results of two inner SELECTs - but the trick is that the second SELECT is offset by one row, so you achieve the requirement of comparing row 1 against row2, etc.

Answer 3

I think you are complicating things with the temp table. It can be made using a cursor and 2 temporary variables. Here is the pseudo code:

declare

v_temp_a%xyz; v_temp_b%xyz; i number; cursor my_cursor is select xyz from xyz;

begin

i := 1;

for my_row in my_cursor loop if (i = 1) v_temp_a := my_row; else v_temp_b := v_temp_a; v_temp_a := my_row;

/* at this point v_temp_b has the previous row and v_temp_a has the currunt row compare them and put whatever logic you want */

end if

i := i + 1;

end loop

end

Answer 4

I think Analytics might do what you want :

select col1, col2, last(col1) over (order by col1, col2) LASTROWVALUE
from table1

this way, LASTROWVALUE will contain de value of col1 for the last row, which you can directly compare to the col1 of the current row.

Look this URL for more info : http://www.orafaq.com/node/55

Answer 5

Hi all,

So sorry, maybe I am not clear with my question. Here is a sample of the result that I am trying to achieve. TABLE X

Column A B C D

   100      300 99      T1
   100  300 98  T2
   100  300 97  T3
   100  100 97  T4
   100  300 97  T5
   101  11  11  T6

column A is the primary key of the table. Column A has duplicates because table X is an audit table that keep tracks of all changes.column D acts as the timestamp for that record. For my query, I am only interested in changes in column A,B and D. After the query I would like to get the result as below:

Column A B D

   100      300     T1
   100  100 T4
   100  300 T5
   101  11  T6

SO SORRY...do not know why the alignment went off..