I have two tables in a database. The first contains rows of string data that I want to compare against itself. The second stores the results.
Table Name APP.ABC
ID
StringData
Table Name APP.RESULTS
ID
StringData_A_ID
StringData_B_ID
Result
I am iterating over the data comparing each row of StringData to each other row of StringData using the following two SELECT statements;
Outer iteration
SELECT ID, StringData FROM APP.ABC
Inner Iteration
SELECT ID, StringData FROM APP.ABC where ID != " + String_A_ID
String_A_ID & String_B_ID hold the ID's of the Strings to be compared.
Strings are not compared to themselves because of the second SELECT statement above however I think I can halve the amount of processing necessary if I can remove duplicate comparisons. These are ID's for sample results (with the actual result removed);
AB
12 (String ID 1 compared to String ID 2)
13
14
15
21 (String ID 2 compared to String ID 1)
23
24
25
etc.
The two lines in red above are duplicates of each other and I only need the first one. How can I prevent the second comparison using a join on the RESULTS table in either of the SELECT statements above?
Any other ideas ?
remove duplicates from iteration problem
Posted - 23 March 2010 : 11:32:06
