Here is a correct one to solve your problem: select b.city, b.name, b.numbeds, count(distinct a.numbeds) c from hospitals a, hospitals b where a.city = b.city and a.numbeds>=b.numbeds group by b.name, b.city, b.numbeds having c=1;
-----Original Message----- From: Myoung-Ah KANG [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 8:45 AM 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