I am studying SQL , and within a series of exercises I came across one that poses 2 (two) tables. The first Animals whose structure is "name, type, country_id" and the other is Countries composed of the columns "id, country". The first table has 5 records per fault, and the second three records. The goal is to insert a new record into the Animals table and then display a new table whose results are only "name, type, country".
After entering the record without problem, to view the results I do the following:
SELECT Animals.name, Animals.type, Countries.country FROM Animals INNER JOIN Countries ON Animals.country_id = Countries.id ORDER BY country ASC ;
Query that returns me:
--------------------------------------------
Candy | Elephant | India
--------------------------------------------
Bert | Tiger | India
--------------------------------------------
Vova | Bear | Russia
--------------------------------------------
Pop | Horse | USA
--------------------------------------------
Merlin | Lion | USA
--------------------------------------------
Slim | Giraffe | USA (registro que inserté yo)
THE PROBLEM is that the platform that proposes this to me asks me for the results to be strictly as follows:
--------------------------------------------
Candy | Elephant | India
--------------------------------------------
Bert | Tiger | India
--------------------------------------------
Vova | Bear | Russia
--------------------------------------------
Slim | Giraffe | USA (registro que inserté yo)
--------------------------------------------
Merlin | Lion | USA
--------------------------------------------
Pop | Horse | USA
Because of this, I can't move forward. What other alternative exists, knowing that I cannot alter the tables to my liking?
According to the second result that you are asked for, and seeing the first image, the only possible order criteria would be:
Sort in ascending order by
country
Sort in descending order by a hypothetical autoincrementing column in the table
Animals
(we'll imagine that column is namedid
). If you order by country first and then by descending id you would have:So the query would be like this: