I have a table that look like follows

TABLE: History
{
history_item_id (autonumber)
id (primary key)
label
street name
street num
old street name
old street num
lastupdated
}

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"

34|1|test1|john street|23|john street|45|2003-08-03
35|1|test1|john street|56|john street|23|2003-08-05
36|1|test1|john street|523|john street|56|2003-08-05
37|2|test2|water street|1|water street|2|2003-08-05
38|2|test2|water street|11|water street|1|2003-08-05
39|2|test2|water street|12|water street|11|2003-08-05

the query should return:
36|1|test1|john street|523|john street|56|2003-08-05
39|2|test2|water street|12|water street|11|2003-08-05

If someone could help me out i would be very grateful
Thanks in advance !!!

the example is hard to understand. the data is mostly the same.

I am not sure what you are asking. Can you make it more clear?

Hi,

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.

Chrs,
Ash

see 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Field

rudy