Comparing rows in table for differences between fields

Question

I have a table (client) with 20+ columns that is mostly historical data.

Something like: id|clientID|field1|field2|etc...|updateDate

If my data looks like this:

    
    10|12|A|A|...|2009-03-01
    11|12|A|B|...|2009-04-01
    19|12|C|B|...|2009-05-01
    21|14|X|Y|...|2009-06-11
    27|14|X|Z|...|2009-07-01

Is there an easy way to compare each row and highlight the differences in the fields? I need to be able to simply highlight the fields that changed between revisions (except for the key and the date of course)

There may be multiple fields updated in each new row (or just one).

This would be on a client by client basis so I could select on the clientID to filter.

It could be on the server or client side, which ever is easiest.

More details I should expand my description a little: I'm looking to just see if there was a difference between the fields (one is different in any way). Some of the data is numeric, some is text others are dates. A more complete example might be:

    
    10|12|A|A|F|G|H|I|J|...|2009-03-01
    11|12|A|B|F|G|H|I|J|...|2009-04-01
    19|12|C|B|F|G|Z|I|J|...|2009-05-01 ***
    21|14|X|Y|L|M|N|O|P|...|2009-06-11
    27|14|X|Z|L|M|N|O|P|...|2009-07-01

I'd want to be able to isplay each row for clientID 12 and highlight B from row 11 and C & Z from row 19.

Answer 1

Any expression in SQL must reference columns only in one row (barring subqueries).

A JOIN can be used to make two different rows into one row of the result set.

So you can compare values on different rows by doing a self-join. Here's an example that shows joining each row to every other row associated with the same client (excluding a join of a row to itself):

SELECT c1.*, c2.*
FROM client c1
JOIN client c2 ON (c1.clientID = c2.clientID AND c1.id <> c2.id)

Now you can write expressions that compare columns. For example, to restrict the above query to those where field1 differs:

SELECT c1.*, c2.*
FROM client c1
JOIN client c2 ON (c1.clientID = c2.clientID AND c1.id <> c2.id)
WHERE c1.field1 <> c2.field1;

You don't specify what kinds of comparisons you need to make, so I'll leave that to you. The key point is that in general, you can use a self-join to compare rows in a given table.

---

Re your comments and clarification: Okay, so your "difference" is not simply by value but by ordinal position of the row. Remember that relational databases don't have a concept of row number, they only have order of rows with respect to some order you must specify in an ORDER BY clause. Don't confuse the "id" pseudokey with row number, the numbers are assigned as monotonically increasing only by coincidence of their implementation.

In MySQL, you could take advantage of user-defined variables to achieve the effect you're looking for. Order the query by clientId and then by id, and track values per column in MySQL user variables. When the value in a current row differs from the value in the variable, do whatever highlighting you were going to do. I'll show an example for one field:

SET @clientid = -1, @field1 = '';
SELECT id, clientId, field1, @clientid, @field1,
  IF(@clientid <> clientid, 
    ((@clientid := clientid) AND (@field1 := field1)) = NULL,
    IF (@field1 <> field1, 
      (@field1 := field1), 
      NULL
    )
  ) AS field1_changed
FROM client c
ORDER BY clientId, id;

Note this solution is not really different from just selecting all rows with plain SQL, and tracking the values with application variables as you fetch rows.