Creating new table from distinct entries
| From: | Alan Milnes | Date: | Thu, 19 Oct 2006 17:02:50 +0000 |
| Subject: | Creating new table from distinct entries | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-243354@lists.php.net to get a copy of this message | ||
PHP 4.4.4
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:-
$myquery="CREATE TABLE mystats SELECT *, DISTINCT MyID FROM oldstats WHERE MyError IS NULL ORDER BY MyChange DESC ;";
$myresult = mysql_query($myquery);
but I am getting an MySQL error #1064.
Any ideas or suggestions as to where I am going wrong?
Alan