RE: The Row Holding the Maximum of a Certain Column

Hi Alex,

> The mysql documentation also states that you must now, i.e. 
> with version
> 4 and above, use a GROUP BY clause with your max statement, 
> otherwise you will get an error. OOo  translates this error 
> with the message you see above.
> 
> Note also that MAX() only works for certain data types.

Are you sure?

1
The sql stmt:

SELECT `Date` FROM `tablename`
where `date` = (select max(date) from`tablename`)

works perfectly in 4.1.14, but not via Base

2
I since found out that the same query runs fine in Base when I use the "Run
SQL command directly option"

3
These are extracts from the latest MySQL manual:

A
About This Manual
This is the Reference Manual for the MySQL Database System. It documents
MySQL up to Version 5.0.9-beta, 

B
3.6.1. The Maximum Value for a Column
"What's the highest item number?"
SELECT MAX(article) AS article FROM shop;

C
3.6.2. The Row Holding the Maximum of a Certain Column
"Find number, dealer, and price of the most expensive article."
In standard SQL (and as of MySQL 4.1), this is easily done with a subquery:
SELECT article, dealer, price FROM shop
WHERE price=(SELECT MAX(price) FROM shop);


None of the above needs a GROUP BY clause????

Cheers
Gert