Little SQL challenge

[petitduc]

This is a little SQL question whose answer is not as trivial as I thought it was a few days ago.

Write a query that fetches the row or rows that have the maximum value for a given column in a table.
To make it clear here is an imaginary table with sample data :

Table players :

id name rating

1 Robert 1800
2 Henry 1600
3 George 1900
4 Paul 1900
5 Daniel 1700
6 Clara 1900

The query would fetch the people that have the highest rating in the table : in this case lines 3 and 4 and 6

Of course, with sub-selects, this is trivial, so the condition is : no sub-selects : it must work in MySQL 3.23.xx
Also, you don't know how much rows have that maximum, so ORDER BY... LIMIT wouldn't work here.
Your solution should be as standard as possible, working with at least one RDBMS other than MySQL.
(Ok, if you don't have time to test this, post what you have 🙂)
I have some answers but it took me a while to find them and I'm curious of how other people would solve this

🆒

[LordShryku]

Originally posted by petitduc
I'm curious of how other people would solve this

🆒

I wouldn't use MySQL. A subquery is just the natural thing to do here, and of all the major databases, MySQL is the only one that can't handle it.

[Pig]

$result = mysql_query("SELECT MAX(rating) FROM table");
$row = mysql_fetch_row($result);
$max_rating = $row[0];
$result = mysql_query("SELECT id, name FROM table WHERE rating = $max_rating");