id (primary key)
old street name
old street num
The table is basically used to keep a log of all changes made to another table i use. I wanted to run a query that would generate the latest record for each id... if my table looked like the one below for eg"
Just to clarify... are you trying to select the most recent entry by each user? If this is the case, you could try the following...
DROP TABLE IF EXISTS $myTable;
CREATE TEMPORARY TABLE $myTable (SELECT * FROM history ORDER BY userId, history_item_id DESC);
SELECT * FROM $myTable GROUP BY id;
Not sure if you can do this in one select statement (without subselects), as mysql can't order within groups... only the returned group values themselves... which would mean you keep getting the first sequenced record for each grouped user. Someone may believe this is not the case... anyone wish to clarify?
I have used the history_item_id for sorting, which assumes it is unique and sequential, as the lastUpdated values you have seem to be of type 'date'. I would probably change your lastUpdated column type to 'datetime' and insert a time component with each date... then change the history_item_id value in the "CREATE TEMP..." statement above to lastUpdated... so you are truely ordering by the date value.