Is this what you're looking for? mysql> select city,name,max(numbeds) from hospitals group by city; +-----------+----------+--------------+ | city | name | max(numbeds) | +-----------+----------+--------------+ | Lyon | Tonkin | 300 | | Marseille | Clairval | 150 | | Paris | AAA | 1500 | +-----------+----------+--------------+ 3 rows in set (0.00 sec)
Cheers, Andrew Sql,query -----Original Message----- From: Myoung-Ah KANG [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 09 October 2002 16:45 To: [EMAIL PROTECTED] Subject: Question about solving a query ! Hello, I have a problem to treat a query with MySQL because MySQL does not permit nested SELECT clauses. The schema of the table is : Hospital num | name | city | numbeds ---------------------------------------- 10 | Clairval | Marseille | 70 20 | Notre-Dame | Marseille | 150 30 | Tonkin | Lyon | 90 40 | Charpennes | Lyon | 300 80 | AAA | Paris |1500 90 | BBB | Paris |1400 100| CCC | Paris | 300 The query is: "For each city, what is the name of the hospital having the highest number of beds ? ' . In fact, I can write (if I do not use MySQL): Select name From Hospital Where (city, numbeds) In (Select city, MAX(numbeds) From Hospital Group By city); But it does not work with MySQL. I'm looking for a MySQL version of this query, I will be very grateful if you help me !!! Thank you !! --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
