I'm storing grid data in a database, but "journaling" each cell to allow retrieving the grid state at a particular time. The initial storage uses the columns name, date, row, col, value.
To change a cell value I just insert a row with the row, col, and value set and date set to NOW(). To delete, value is set to NULL.
But to get the current cell data is proving too difficult. I know I want to do something like:
SELECT row, col, value FROM journal WHERE name = ? GROUP BY row, col HAVING date = MAX(date)
but of course this won't work since the value column is not part of the GROUP BY.
I could get a value based on the date, but need to use the row and col values as well, and each row and column may have a different MAX(date).
I can't be the first person to try journaling data in MySQL, so a solution to the query or a pointer to teh right way to do this would be accepted.
Thanks for any help.