Here's some bogus data that might help explain what I'm dealing with:
\na b date c d e\n----------------------\na 1 1999 1 2 3\na 2 2000 1 2 3\na 1 1999 2 2 3\na 1 1999 2 3 3
The new table will have primary key (a,b), and is supposed to contain all the data in the most recent record from the original table for each a,b item.
\nselect B.* from \n (select a,b, max(date) as xDate from tbl group by a,b) as A,\n tbl as B where B.date = a.xDate
max(date) works for a,2 because the data is nice and there is only one last record, but for a,1 it will return 3 records. To pare that down, I decide that c is the next most important field and some values are more important than others, and for the sake of simplicity max(c) will return the most important value.
\nselect B.* from\n (select * from\n (select a,b, max(date) as xDate from tbl group by a,b) as AA,\n (select a,b, date, max(c) as xC from tbl group by a,b,date) as BB\n where BB.date = AA.xDate\n ) as A, tbl as B where B.c = A.xC
That still leave duplicates, so I pick the next most important field. As the query takes three hours to run, I start to think that I'm going about this the wrong way, but I don't see another way of doing it.