List:General Discussion« Previous MessageNext Message »
From:Alan Milnes Date:October 23 2006 7:17pm
Subject:Creating new table from distinct entries
View as plain text  
MySQL 4.1.21-community-nt

I have a table in my database that has a Primary key on 2 fields  (MyID 
and MyChange) and a field that indicates if there is a problem with the 
record (MyError)- I want to create a new table that only has unique 
MyIDs and where there is more than 1 I only want the record with the 
highest MyChange number.  The table has about 50 fields so I have the 
following code:-

CREATE TABLE mystats SELECT  *, DISTINCT MyID FROM oldstats WHERE 
MyError IS NULL ORDER BY MyChange DESC ;

but I am getting an MySQL error #1064.

Any ideas or suggestions as to where I am going wrong?

Alan
Thread
Creating new table from distinct entriesAlan Milnes23 Oct
  • Re: Creating new table from distinct entriesVisolve DB Team25 Oct