Animal weights
Question
Suppose you're given the below tables, containing animal weights, as well as a directory of zoos:
Table name: animal_weights
| animal_ID | weight_lbs | zoo_ID |
|---|---|---|
| 09992 | 1040 | 012 |
| 099929 | 1090 | 012 |
| 012993 | 2190 | 011 |
| 009821 | 750 | 011 |
| 096673 | 580 | 011 |
| 01411 | 690 | 012 |
| 01415 | 695 | 009 |
| 01410 | 690 | 009 |
| 01117 | 1000 | 009 |
Table name: zoo_directory
| zoo_ID | |
|---|---|
| 012 | Detroit Zoo |
| 011 | LA Zoo |
| 009 | NY Zoo |
Given the above, write a SQL query to return the second heaviest animal in the table from each zoo. In addition to the zoo_ID, return the zoo_name from the zoo_directory table.
Click here to view these tables in an interactive SQL fiddle.