Re: Question about solving a query !
Myoung-Ah KANG wrote: The query is: For each city, what is the name of the hospital having the highest number of beds ? ' . Select name From Hospital Where (city, numbeds) In (Select city, MAX(numbeds) From Hospital Group By city); select name, city, max(numbeds) from hospital group by city should work too, shouldn't it? -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
RE: Question about solving a query !
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
RE: Question about solving a query !
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
