title: Finding "duplicate" rows that differ in one column
body: <p>I have a table like the following in MySQL 5.1:</p>
  
  <pre><code>+--------------+----------------+------+-----+---------+----------------+
  | Field        | Type           | Null | Key | Default | Extra          |
  +--------------+----------------+------+-----+---------+----------------+
  | log_id       | int(11)        | NO   | PRI | NULL    | auto_increment |
  | date         | datetime       | NO   | MUL | NULL    |                |
  | date_millis  | int(3)         | NO   |     | NULL    |                |
  | eib_address  | varchar(20)    | NO   |     | NULL    |                |
  | ip_address   | varchar(15)    | NO   |     | NULL    |                |
  | value        | decimal(20,10) | NO   | MUL | NULL    |                |
  | application  | tinyint(4)     | NO   |     | NULL    |                |
  | phys_address | varchar(20)    | NO   |     | NULL    |                |
  | orig_log_id  | bigint(20)     | NO   |     | NULL    |                |
  +--------------+----------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>In this table, <code>log_id</code> and <code>orig_log_id</code> are always unique.  It is possible that two rows may have duplicate values for any of the other fields, though.  Ignoring the <code>*log_id</code> fields, our problem is that two rows may be identical in all other columns, but have differing values for <code>value</code>.  I am trying to figure out the correct SQL query to identify when two (or more) rows have identical values for <code>date</code>, <code>date_millis</code> and <code>eib_address</code>, but different values for <code>value</code>, <code>log_id</code> and <code>orig_log_id</code>.  So far, I've been able to come up with a query that accomplishes the first clause in my previous sentence:</p>
  
  <pre><code>SELECT main.* 
  FROM sensors_log main
  INNER JOIN 
      (SELECT date, date_millis, eib_address 
      FROM sensors_log 
      GROUP BY date, date_millis, eib_address 
      HAVING count(eib_address) &gt; 1) dupes 
  ON main.date = dupes.date 
      AND main.date_millis = dupes.date_millis 
      AND main.eib_address = dupes.eib_address;
  </code></pre>
  
  <p>However, I can't seem to figure out when <code>value</code> differs.  I at least know that just throwing <code>AND main.value != dupes.value</code> into the <code>ON</code> clause doesn't do it!</p>