title: Remove duplicate rows where col1 and col2 are equal but reversed
body: <p>I have a query that has the following results.  How can I modify this query to remove duplicate rows.  In this case, the duplicates would be &quot;row1.antecedent = row2.consequent and row2.antecedent= row1.consequent&quot;.  I only need one such row, not both in my results.  Also, this logic has to be directly added in the query, not the encompassing programming language.</p>
  <p><a href="https://i.stack.imgur.com/CAU5m.png" rel="nofollow noreferrer">Query Results</a></p>
  <p>So let's assume this my table with the data as shown.  I would like to select rows from this table such that only rows with rule_id = 943,945 and 979 are selected.  That's because 944 gives me same results as 943; 946 is same as 945 and 980 is same as 979, except with the antecedent and consequent reversed.  Please ignore the value CONF. That would be different.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>RULE_ID</th>
  <th>ANTECEDENT</th>
  <th>CONSEQUENT</th>
  <th>SUPP</th>
  <th>CONF</th>
  <th>LIFT</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>943</td>
  <td>3017</td>
  <td>3014</td>
  <td>0.69%</td>
  <td>65.59%</td>
  <td>55.78</td>
  </tr>
  <tr>
  <td>944</td>
  <td>3014</td>
  <td>3017</td>
  <td>0.69%</td>
  <td>58.46%</td>
  <td>55.78</td>
  </tr>
  <tr>
  <td>945</td>
  <td>3018</td>
  <td>3014</td>
  <td>0.81%</td>
  <td>55.60%</td>
  <td>47.29</td>
  </tr>
  <tr>
  <td>946</td>
  <td>3014</td>
  <td>3018</td>
  <td>0.81%</td>
  <td>68.86%</td>
  <td>47.29</td>
  </tr>
  <tr>
  <td>979</td>
  <td>3055</td>
  <td>3015</td>
  <td>0.62%</td>
  <td>60.35%</td>
  <td>68.9</td>
  </tr>
  <tr>
  <td>980</td>
  <td>3015</td>
  <td>3055</td>
  <td>0.62%</td>
  <td>70.30%</td>
  <td>68.9</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>UPDATED QUERY - THIS WORKS</p>
  <pre><code>select * from (
  SELECT 
  RULE_ID,
  A.ATTRIBUTE_SUBNAME ANTECEDENT,
  C.ATTRIBUTE_SUBNAME CONSEQUENT,
  CONCAT(TO_CHAR(RULE_SUPPORT*100,'990.99'),'%') Support_Frequency,
  CONCAT(TO_CHAR(RULE_CONFIDENCE*100,'990.99'),'%') Confidence_Association,
  ROUND(RULE_LIFT, 2) Lift_Likelyhood
  FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('AR_SH_SAMPLE')) T,
  TABLE(T.CONSEQUENT) C,
  TABLE(T.ANTECEDENT) A
  ) where rule_id in 
  (
  select min(rule_id) from
  (
  SELECT 
  RULE_ID,
  A.ATTRIBUTE_SUBNAME ANTECEDENT,
  C.ATTRIBUTE_SUBNAME CONSEQUENT,
  CONCAT(TO_CHAR(RULE_SUPPORT*100,'990.99'),'%') Support_Frequency,
  CONCAT(TO_CHAR(RULE_CONFIDENCE*100,'990.99'),'%') Confidence_Association,
  ROUND(RULE_LIFT, 2) Lift_Likelyhood,
  (CASE 
  WHEN A.ATTRIBUTE_SUBNAME &gt; C.ATTRIBUTE_SUBNAME THEN CONCAT(A.ATTRIBUTE_SUBNAME,C.ATTRIBUTE_SUBNAME)
  WHEN C.ATTRIBUTE_SUBNAME &gt; A.ATTRIBUTE_SUBNAME THEN CONCAT(C.ATTRIBUTE_SUBNAME,A.ATTRIBUTE_SUBNAME)
  END) CAT
  FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('AR_SH_SAMPLE')) T,
  TABLE(T.CONSEQUENT) C,
  TABLE(T.ANTECEDENT) A
  )
  group by CAT
  )
  ORDER BY RULE_ID
  </code></pre>