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.