MYSQL :: Invalid Distinct Recordset Returned On An Indexed Column

Wanting Distinct Rows (by Only One Column), Not Sure How To Do It
I have a table `logins` with id, name, ip, mac, date

ip and mac are both tracking information for name and I'm trying to write a query that, when given a name, selects all names that person has logged in with by cross-checking their ip and mac with every record we have for that name

I have this query so far but it gives duplicates in the name field. What I'm trying to do is get it to give me each name only once and the most recent date associated with that name.

SELECT *
FROM `login_history`
WHERE `ip`
IN (

SELECT `ip`
FROM `login_history`
WHERE `name` = 'Name'
)
OR `mac`
IN (

SELECT `mac`
FROM `login_history`
WHERE `name` = 'Name'
)

Distinct Column In Multi-Table Query
I want one article from each 'sub topic'. Each sub topic is given a 'sub_id'.

PHP

$garticle = mysql_query("SELECT DISTINCT(feeds.sub_id),
articles.title, articles.feed_id, articles.abstract,
articles.link, articles.date, site.articles,
$formula AS importance
FROM articles,feeds,site
WHERE articles.date > DATE_SUB( now() , INTERVAL 1 day )
AND articles.feed_id=feeds.feed_id
AND feeds.site_id = site.site_id
ORDER BY articles.article_id DESC LIMIT 16", $connection)
or die(mysql_error());

The article has a 'feedIid', this JOINS the feeds table. The feeds have a 'sub_id'. I only want the 16 most recent articles from DISTINCT 'sub_id's

SELECT DISTINCT On A Varchar Column (big Table)
I have a table with 10 million rows in it. Now, I need to export a list of unique values from a varchar column. Normally I would do:

SELECT DISTINCT MyColumn INTO OUTFILE '/dumpfile.txt' FROM MyTable

But this is VERY time consuming and slows my PC down.

Can you give me an advice about what should I attempt to make it last the less?
For instance, should I create an index on MyColumn AND THEN execute the SELECT DISTINCT?

I may look silly, but I also tried using a Memory table thinking I would cut the indexing computation time but my RAM is not big enough to contain all the rows.

Retrieving The Quantity Of Each Distinct Number From A Column
I have this table in which each row is associated with a category number.
The category number is NOT unique, so basically more than one row in the
table can have the same category number. Now, I want to know how many times
each category number appears in the table. For example, if i have a category
number, say, 5, i want to know how many '5's there are in the same table. I
want to do this all at once and place the results in 2 columns, one for the
category number and the other for the quantity associated with each number.
how do I do that with mysql? (I can process it with php of course, but i
just think it's cool to do it with mysql), is it possible to do it with one
statement? Does it involve the Count function?

Indexed Totals
don't know if that subject is correctly put, but here's what i'm trying to accomplish:
I want to be able to tell how many rows in a given table, and for a given INDEXED
column, carry any given ID.
Example: suppose I have a field named 'IDNumeric' defined as decimal(5,0). Now suppose
one ID is: '56007'. I want to be able to tell how many rows in the entire table have
that ID. I know that I can use the select keyword, but i'm wondering if there's another
way, because the table i'll be doing this for can be up to 350 Million rows, and the
vast majority (probably close to 99.9%) of the rows will have mutually exclusive IDs.
I am only concerned with that small percentage of rows that have duplicate IDs

Pivoting A Column W/ Undetermined Number Of Distinct Values
Suppose I have 2 tables and each table has 2 columns (name, category). I want to make a script that takes in one parameter from the user specifying which table they want to use and then give them a table that has as many columns as there are distinct values within the category column and a count of all people belonging to that distinct category. Code:

Indexed Search Capability
I'd like to have indexed search capability on column A, column B, or
columns (A,B) for a given table. According to the MySQL manual, a
multi-column index of (A,B) will provide "leftmost prefix" indexing, so
that I can search on column A uniquely or columns (A,B) together, but
not on column B uniquely. Therefore, I would need a second index on
column B.
My question: would there be any benefit, whatsoever, to having a
separate index just for column A, or is that completely redundant to
the multi-column index (A,B)?

Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'.
About half the rows are 'yes' and half are 'no'.
Table is indexed on 'stamped'.
We just need the count on 'yes'.

SELECT count(*) from T where stamped='yes'

or <>'no' or >'n'... is very slow.

Is there a better approach / query to get a fast result?

MySQL Timing Out? Really Slow Queries, Already Indexed.
For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got:

Uptime: 220372
Threads: 39
Questions: 18748899
Slow queries: 808
Opens: 28723
Flush tables: 1
Open tables: 4096
Queries per second avg: 85.078

The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling.

Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096:
query_cache_min_res_unit = 4096
table_cache = 4096
transaction_prealloc_size = 4096

Do any of those have anything to do with this? Am I even close? Are any of those values from mysql_stat a big deal?

Recordset
Not sure I'm in the right forum, but anyway:
Is there a way of updating a recordset of db in access, but not to update the database itself?
I'm using asp pages.
To be clearer: I need to update a certain field in a database,
just as long as the asp page is accessed, but I don't want this update to take effect when a user is out of this asp page.
So I thought of creating A RECORDSET and update there this certain field in part of the records, but want no effect on the real db.
Btw - For what u r about to propose. Do I need a write permission in the database, if not really updated?

Recordset Error
I just installed MySQL on Windows 2000 Server, and am trying to rewrite my ASPs to interface with it. When I try to access one of the columns in the recordset, Microsoft Script Debugging pops up with an "Exception Occurred" error. In the command window I type in, and receive back, the following:

? objrs("FilePath")
[error]

? objrs("ImageType")
Reg

? objrs("FilePath")
G:/Documents and Settings/disk42/Desktop/analog.txt

ImageType happens to be the column right before FilePath. I can't think of any reason why I can't access FilePath until after I've accessed ImageType. Anyone else run into this problem, or know what may cause it?

Recordset Length In PHP
I have the following peice of code in a PHP page:

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$categoryName = $row[1];
$categoryDescription = $row[2];
$pos = count($aryCategories);
$aryCategories[$pos][0] = $row[3];
$aryCategories[$pos][1] = $row[4];
$aryCategories[$pos][2] = $row[5];
$aryCategories[$pos][3] = $row[6];
}
if (count($aryCategories) == 0) {
header('location:directory.php');
}

Is there a way I can test the length of the recordset to trigger the redirect, rather than building the array (or not) and testing the length of that. Something like:

if (rs.length < 1) {
//redirect
} else {
//while loop
}

Also, while you're looking, is there a way I can set the $categoryName and $categoryDescription variables before the while loop? These values are the same for every row returned in the recordset so I don't need to reset them to the same value every time in the while loop. Something like:

if (rs.length < 1) {
//redirect
} else {
$categoryName = firstRowFirstField;
$categoryDescription = firstRowSecondField;
//while loop
}

Clean Up Recordset
I have a basic query that returns a list of data. My problem is that the query is returning multiple copies of a given "t.clinical_specialty". This makes sense, because there may be more than one "s. course_ID" that matches up. How do I change my query so that only one copy of any given "t.clinical specialty" appears in my list? Code:

SELECT t.clinical_specialty
FROM clinical_specialty AS t, course_status AS s
WHERE t.course_ID = s.course_ID
AND s.status = 'online'
ORDER BY t.clinical_specialty ASC

SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have;

SELECT DISTINCT `field1`, `field2`, `field3` FROM table1

This returns what I need. There is also another field (field4) which I also want to select, but not distinctly.

Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1`

The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct)

I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me.

How can I display this not-wanted-distinct field in a distinct query?

Select Distinct And Include Non-distinct Columns
I have a publication table that tracks the products assigned to various publications.

I want to select all of the distinct products, based on product_ID, assigned to a specific publication but I also want to return additional columns that do not need to be distinct.

If I use the following select:

Select distinct publication.product_ID, publication_ID.code, publication.region from publication where publication_ID = '12'

I would get a list showing the three columns that I want to see which are product_ID, publication_ID and region for publication 12 but I get too many items as the distinct function means that all three of these columns must be distinct and I only need the product_ID to be distinct.

So how do I find all of the distinct product_ID but also show other columns such as region?

Do I need to do this with some kind of self-join?

Invalid Use
I am having a problem with group. Following is my query but it gives an error of "Invalid use of group".

SELECT refMember.MemberAcroName, refMember.MemberName, Count(DistrictLevelInformation.FormID) AS Spread
FROM (refMember LEFT JOIN MainInformation ON refMember.MemberID = MainInformation.MemberID) LEFT JOIN DistrictLevelInformation ON MainInformation.ID = DistrictLevelInformation.FormID
GROUP BY refMember.MemberAcroName, refMember.MemberName, MainInformation.qYear, MainInformation.Quarter
ORDER BY MainInformation.qYear DESC , MainInformation.Quarter DESC , Count(DistrictLevelInformation.FormID) DESC

Invalid Use
I have tested my query on MySQL 5 and it works, sadly the server it is to be run on only has 3.
I was just wondering what I need to update in the following query to get it working on 3 or if it is even possible to get it running on 3.

Code:

.....
and keyword IN ('keywords')
group by
people
having count(*) >= 1
ORDER BY people ASC

Add A Record To An Existing Recordset?
I have a MySQL table with about 30 records, I use a form to insert data into the first 24. (like; Name, Phone, etc...)

The last 6 (Photo1, Photo2, Photo3, Photo4, Photo5, Photo6) are the names of uploaded images (which are uploaded from a seperate form.

How do I (Or what MySQL statement do I use) to insert the 6 names into the recordset?
(Each image name will be saved into a php variable. ($Img1, $Img2, $Img3, $Img4, $Img5, $Img6)

Table/recordset Problem
After building a form in php the MySQL table I was adding to quit accepting data entry on the 16th record. Now it is stuck however I was able to edit records. If I delete and rebuild the table it won't accept any new record entries.

Not sure why this is? I recently shifted from ASP to PHP and an apache server and kind of relearning everything.

To Omit Records From A Recordset
Does anyone know how to configure a table so that you keep all your data in the database but perhaps omit certain records from showing on the website? Like whether the row in the table is an active or in-active row.

Update A MySQL Recordset Through ADO?
How do I force an update to a MySQL recordset through ADO on an open form?

recordset.update does not appear to be working for me. Here is why:

I am using MS Access to build forms and ADO to populate the controls on that form.

I want a user to be able to edit data on a form, move to a new record and then move back again in order to find the record has been updated without having to close down the form.

If I set the ADO connection object to "currentproject.connection" (with linked tables) everything works fine. However, it is when I change the connection object to a full MySQL connection string that I cannot seem to dynamically update the database through a form.

Does anyone have any ideas?

Recordset Paging - Putting It All Together
I have just created a library database which includes 8084 books, publications, periodicals, etc. My client would like to see this database searchable in various ways which I am now able to do all by myself (slow learner) but I'm now to the point where I'm trying to put all the bits and pieces together for a good recordset paging system.

I understand that I will probably first use mysql_num_rows to retrieve the number of rows that will be resulted from the search. Then I think I have to use LIMIT to specify how many rows to display on each page of results (lets say 10).

I can use the offset with LIMIT so that my first query would be to select the first 10 (ie. LIMIT 0, 10) then another to select the next 10 (ie. LIMIT 11, 10)... but with 8084 records how will I know how many queries to build?

And how does one create the links where it shows how many result pages there will be on a query? For example this site uses exactly what I'm looking to do with each thread... it shows how many pages there are and they are linked to the next page, x of y pages, etc.

Is there something out there that will help me put this all together? Could someone kindly give me a kick-start?

How To Use The Ado Recordset Function, Addnew
After connected, and opened a recordset, the table has a primary key;
I want to add a rowdata, use the addnew,

like that
m_pRecordset->AddNew();
then a error happened, i dont know the reason.

Recordset Handling In MySQL/PHP
I am using PHP / mySQL to write a Quiz. I currently load all the questions into a recordset. I then, iteratively, call this page, displaying the first question and possible answers, then the second question and possible answers and so on.
While this works fine it is obviously dead ineffecient - I load the recordset with the same set of questions each time. Also, the questions come out in the same order each time.

I know you can use the LIMIT clause to get a record / n records at a time and you can also use RAND() to mix the records.
Ideally, what I would like to do is one select statement with a RAND() which returns all the questions in a different order time the quiz is started. However, once the recordset is loaded, I want to use it and get the next question on each subsequent call of the file.

Updating A JOINed Recordset
Are there any tricks in updaitng a JOINed recordset?
I joned to tables and when I try to change a field on the recordset and
update it, I get this error:
"Unknown column 'CCDE' in 'where clause'.
CCDE is a field from one of the table and is not a field I am updating.

Invalid Index
I try to used the MyPhpAdmin 2.8.2.4 with PHP 4.4.4, MySQL5.0.2.4 and Apcache2 and I got this error, 'invalid Index'

Invalid Use Of Group By
I have the following query:

SELECT CLAIMPOLICYNUMBER AS Plan, PROVIDERSPECIALTY AS Specialty, TINNUM AS Tin, SUBTINSEQNUM AS SibTin, CHARGETYPECATEGORY AS ChargeCategory, SUM(BILLEDCHARGE) AS Billed, SUM(PAID) AS Paid, " + _
COUNT(DOCUMENTNUM) AS ChgCount
FROM BAT
WHERE CASENUM='0000911'
AND CHARGETYPE <> 'DENTAL' AND CHARGETYPE <> 'VISION' AND CHARGETYPE <> 'ADMIN'
AND PMTDATE >= '2005-06-01' AND PMTDATE <= '2005-06-30'
GROUP BY CLAIMPOLICYNUMBER, PROVIDERSPECIALTY, TINNUM, SUBTINSEQNUM, CHARGETYPECATEGORY
ORDER BY SUM(BILLEDCHARGE)

It works great without the ORDER BY clause, but with it, I get the aforementioned error.

Directory Name Invalid
For testing purposes I have PHP installed and I have MySQL installed on my local laptop. Windows XP Pro

I am just learning both of them. I have read quite a few chapters in the MySQL Manual. Enough to allow me to get around in MySQL and directly create tables an view information in them, upload .txt files into the tables, etc etc.

I am receiving an error when I try to connect to MySQL via a PHP script.
I have no problem connecting to MySQL via Command Line.

The error I receive is "The directory name is invalid."

Here is my code... I am not sure where to put the directory name or where to put the directory path in order to make it valid. I am not sure what directory it is talking about.

<html>
<head><title>Connect to MySQL</title></head>
<body>
<?php
//connecting to the mysql server uses a mysql_connect command.
$link = mysql_connect("locoalhost",$_POST['username'],$_POST['password']) or die("Connect Error: ", . mysql_error());
echo 'Successfully Connected.
';
mysql_close($link);
?>
</body>
</html>

Where exactly would I put the path to the directory or where would I name the directory?

Invalid Syntax
I am having trouble changing my password back to OLD_PASSWORD by the following :

mysql> SET PASSWORD FOR
-> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

When I do it, it says
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'OLD_PASSWORD ('newpwd')' at line 2

Inserting A Record Into An Exhisting Recordset
I have a two page form. The first page captures text information from the user, the user submits the form (which writes the information to a database). I then allow the user to upload up to 6 images, whose names id like to also add to the recordset. Let me give you some information on the table.
It has about 30 records (Name, Email, Phone,....) The last 6 records are (Photo1, Photo2, Photo3, Photo4, Photo5, Photo6) which should be the names of the uploaded images of which i'll use php to upload and grab the image name and store it in these variables ($Img1, $Img2, $Img3, $Img4, $Img5, $Img6)
Here's what I'm trying to have happen, once the images have been uploaded, id like to change the Image records only. The primary key for the recordset will be ID, So I think something like this will be used?
INSERT INTO Providers VALUES
'Photo1' =='$Img1',
'Photo2' =='$Img1',
'Photo3' =='$Img1',
'Photo4' =='$Img1',
'Photo5' =='$Img1',
'Photo6' =='$Img1'
WHERE 'ID' == '$ID'
Is that right?

ADODB.Recordset.1 Error '80004005'
My database completely works in Dreamweaver MX at a local testing level,
but as soon as I upload and access my web pages remotely I get this error on
the browser.

ADODB.Recordset.1 error '80004005'

SQLState: IM002
Native Error Code: 0
[INTERSOLV][ODBC lib] Data source name not found and no default driver
specified

/index.asp, line 5

Multiple Queries In One Recordset/connection
I am doing an insert, for which I want to get the auto-increment value back as a recordet in the same connection, I have the following:

$query = "";
$query = $query . "INSERT INTO ";
$query = $query . "myTable ";
$query = $query . "(name,summary) VALUES ";
$query = $query . "('" . urlencode($_POST["frmName"]) . "','" . urlencode($_POST["frmSummary"]) . )";
//echo "Query: " . $query;
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

I want to add to the query:

$query = $query . "SELECT DISTINCT @@Identity AS 'newId' FROM myTable";

When trying a semicolan seperator, I get an error (this is also true in my query analyser - it doesn't work there either.) Is this even possible, and if so, what am I doing wrong?

Recordset Paging With MySQL & ASP Using LIMIT
I have an asp page which I want to display 10 records with a
'Previous' and 'Next' link underneath for navigating through the rest
of the recordset.

I have the page displaying the first 10 records and the links. The
links work, but the data does not change.

How can I adjust the SQL so that the code re-runs the SQL ? Is there a
way to override the LIMIT or change the way it is written ??

Code:

MySQL Command Issues With An ADO Recordset
I know you will probably say that I should use MS SQL Server DBs with ASP,
but my MySQL and ASP combo works and is affordable for me and up until now
didn't give me any grief until recently.

In essence, the following MySQL query commands cause my ASP to generate a
Type Mismatch error when I try to response.write the result of the query
fields:

Query 1

CONCAT(PICFILEWIDTH,' x ',PICFILEHEIGHT)

I call this query result into a dynamically-created array from a recordset
object, ie arrSQLData (the array) = oRSv.GetRows() (the recordset object),
but when I call say arrSQLData(0,0) it generates the type mismatch.

This used to work fine in the MySQL v4 something that had the crappy
installer, but since they made the new installer (and some other mods) it
causes this problem.

I've checked the MySQL site and it says that the above, which is an int, ' x
', another int is fine, but something in ASP doesn't like the result it
sends back.

Done a straight query from SQLyog and that displays it OK.

Query 2

IFNULL((sp.RRPTAX/sp.RRPNET),'0.00')

same again this generates a type mismatch when it used to work fine.
Obviously the command puts in 0.00 if the tax and net values are null, but
this doesn't work now. It generates a type mismatch.

Has anybody had this problem and fixed it?

Invalid Use Of Group Function
I have the following Sql Statement... I'd like to find the sum of hrs worked by an employee in the month, the required number of hrs, and the sum of the difference between the two, the worked hrs are calculated by subtracting the max vtranstime - min vtranstime and the required hrs are calculated by subtracting max to1 - max from1....

I tried to do the following:

select sum(timediff(max(vTransTime),min(vTransTime))) as worked,
sum(timediff(max(translog.to1),min(translog.from1))) as required,
sum(timediff(sum(timediff(max(vTransTime),min(vTransTime))),
sum(timediff(max(translog.to1),min(translog.from1))))) as overtime,
empid,deptname, firstname,lastname from employee,translog,department where
employee.emp_magnetic_num=translog.vId
group by employee.empid
I am getting the following error :
Invalid use of group function

Invalid Backup File
I am tring to restore a .pmb backup file and getting the message "Invalid backup file". I am very new to myssql so I don't have a clue what that means. Wrong version of mysql? Corrupted file?

Invalid Use Of Group Function
Using mysql 4.0

I get the following error message

"Invalid Use of group function"

with the following code:

update custRP c, tbl_acctbal a
set c.rpBal = sum(a.currRPBAL)
where c.custid = a.custid

Invalid Argument While Using BCP (Script)
This will be a 50/50 question. It's either something that the SQL experts can answer or a scripting forum. I'm new to SQL and just coming to grips with getting familiar with scripts. I've developed a small script for my workplace that will connect to a remote server and extract the specified table that I want, zip it up and then copy it back to my local machine. This works fine until it tries to run the bcp command.
If I tivoli to the remote machine and type the bcp command in command prompt it works fine. However when executed from the script it throws an invalid argument and seems to refer to the "-" minus I place in front of the U (user) or P(pwd). The script echos the users table request to a file which is then copied to the remote server (SQLtable.cmd) and then PSEXEC is called to connect to the server and run the script. e.g. psexec \%Server% -u %user% -p %password% "C: empSQLtable.cmd"
PSEXEC then returns the following error.

C:WINNTSYSTEM32>Bcp FrontOff..SHIFT_TER_DATA out C: empdmpnlSHIFT_TER_DATA.dat Ã»Usa Ã»Ppwd (chopped off the -n)
Unknown argument 'Â¹Usa' on command line.

Sorry about all the data, just hope someone can help. Must be an easy way. The small script which psexec is executing contains two simple lines. Bcp FrontOff..SHIFT_TER_DATA out C: empdmpnlSHIFT_TER_DATA.dat -Usa -Ppwd -n
wwzip -9 C: emp\%username%SQLtables.zip C: emp\%username%*.*

Invalid Characters With PHP And MySQL
I am writing about a situation that our client is currently experiencing with Linux Mandrake, Apache, MySQL and PHP. We successfully deployed a website running on this type of system for a period close to two years. It has a HTML GUI component that the client can type or paste content into and it serves-up dynamic pages in PHP from a MySQL database. Recently the web site was hacked by the Travel Bug virus and it forced the client to restore the web site and server software mentioned below.

Original Install
Mandrake 8, MySQL 3.23.32, PHP 4.0.06 and Apache 1.3.26-x.x

Current Install
Mandrake 9.0, MySQL 3.23.52-1, PHP 4.2.3-1.1 and Apache 1.3.26-6.1

The current install has lead to new problems. Content with double and single quotes are showing up as squares or question marks. For example, I have a page that is showing the square symbol in place of double quotes. If I do a view source on that specific page I can see the double quotes but if I telnet into the server and query the MySQL record belonging to that page the square or double quote character is nowhere to found. The following items are the steps we tried to understand why this is happening.

First, we did the following to test the integrity of the MySQL backup file. We Restored the MySQL database on our test environment which has the original software specs mentioned above but on a window server running IIS not apache. I did a restore of the MySQL database file and did not get any invalid characters. Even though the environment isn’t completely identical to the clients we can make the assumption that the dump file wasn’t corrupted.

Second, possibly content editors were copying and pasting from word into the HTML GUI component thus creating these invalid characters. In most cases yes but I personally noticed the invalid character issue with a page that doesn’t use the HTML GUI component.

Third, Possibly the configuration especially the character set from the original version to the current version of MySQL is not configured correctly. We believe this to be the primary issue. The resolution is to configure or reinstall MySQL and restore from backup.

Fourth, has anyone tried the PHP functions to convert the invalid characters. A request was to look into the iconv() function. The iconv library functions convert strings between various character sets encodings. This involves compiling the PHP interpreter which is why I haven’t tested it.

Fifth, Can some other process running on the server be causing this?

Altogether, we soon will begin creating a test server identical to the original settings and step through the install and try to recreate the invalid character issue. But, if anyone has experienced this invalid character issue or has more information that would benefit us your help would greatly be appreciated.

Empty Recordset When Querying A Decimal Field
When I query decimal fields in my tables (i.e. prices and costs), nothing is returned to the recordset. If I change the data type of the fields to varchar or all data types, it's fine. Please can you tell me where I am going wrong, or if there other similar data types that I can use that will give me two decimals.

Have My Recordset Table Show Up In Random Order
I have the following code on my PHP page and my goal is to have the recordset show up in a random order each time the page is visited. I have tried a ORDER BY RAND() and come up with errors only. Is there anyway to find out what code I need to use and where I would place it in this piece of code? Code:

Invalid Use Of Group Function - Max(`mainid`)+1
I am trying to insert a incremented number into the menu table. I will have an autoincremented number as well as the mainid number. When I tested the code that is below, the above error happened. Can someone please tell me the correct way of doing this? I am getting really frustrated with this. Nothing wants to work for me.

require "config.php";
$insert06 = "INSERT INTO `menu` (`mainid`)
VALUES (max(`mainid`)+1)"; // error line
if (mysql_query ($insert06)) {
print "Mainid added.";
$query2 = mysql_query("SELECT mainid FROM menu") or die ("Could not query because: ".mysql_error());
while($row2 = mysql_fetch_array($query2)) {
echo $row2['mainid']." = Mainid";
}
} else {
print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $insert06.</p>";
}
mysql_close();

Group By Returns Invalid Dates
Hi i have one table:

ID, User, Score, Date
1, mac, 1234, 2007-12-09 09:01:07
2,kurca, 3232, 2007-12-05 09:43:57
3, mac, 999999, 2007-11-07 09:44:14

I wan to create a high score list which will represent the best scores but not repeating anyone for example:
1, mac, 999999, 2007-11-07 09:44:14
2,kurca, 3232, 2007-12-05 09:43:57

Now i use:
SELECT ID, User,MAX(Score) as maxScore,Date
FROM `table`
GROUP BY User
ORDER BY maxScore DESC

with this query the score is right but the date is invalid example:
1, mac, 999999,2007-12-09 09:01:07
2,kurca, 3232, 2007-12-05 09:43:57