> For the UPDATE query, I hadn't got there yet as I
> found I couldn't use the subquery on the same
> table, which is what I would have done in SQL
> Server. In fact without the subquery cannot work
> out a way to do it. Open to suggestions...
Write what you would have written in SQL Server, and I'll convert...
> On the ordering of columns in the index, should
> the columns in the GROUP BY comes first in the
> order of the GROUP BY? Then the
> ID_EFFECTIVE_DATE?
Not quite sure what you're asking here... If you're asking whether the order of the columns in the index should depend on the order you have things in the GROUP BY clause, then the answer is it depends. In order to determine the correct order, I'd have to see an example of common queries issued against this and related tables. The more code you can post, the easier I can tell whether the column choice is appropriate...
Cheers,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com