[Programmers / MySQL] Level 1 Sort by Multiple Criteria (59404)
[Programmers / MySQL] Level 1 Sort by Multiple Criteria (59404)
| Rank | Language Used |
|---|---|
| Level 1 | 🖼️ MySQL |
The ANIMAL_INS table contains information about animals that entered the animal shelter. The ANIMAL_INS table structure is as follows, where ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, and SEX_UPON_INTAKE represent the animal's ID, species, intake date, condition at intake, name, and sex/neuter status, respectively.
| NAME | TYPE | NULLABLE |
|---|---|---|
| ANIMAL_ID | VARCHAR(N) | FALSE |
| ANIMAL_TYPE | VARCHAR(N) | FALSE |
| DATETIME | DATETIME | FALSE |
| INTAKE_CONDITION | VARCHAR(N) | FALSE |
| NAME | VARCHAR(N) | TRUE |
| SEX_UPON_INTAKE | VARCHAR(N) | FALSE |
Write a SQL statement that retrieves the ID, name, and intake date of every animal that entered the shelter, ordered by name. However, among animals with the same name, the one whose protection started later should be shown first.
For example, if the ANIMAL_INS table is as follows:
| ANIMAL_ID | ANIMAL_TYPE | DATETIME | INTAKE_CONDITION | NAME | SEX_UPON_INTAKE |
|---|---|---|---|---|---|
| A349996 | Cat | 2018-01-22 14:32:00 | Normal | Sugar | Neutered Male |
| A350276 | Cat | 2017-08-13 13:50:00 | Normal | Jewel | Spayed Female |
| A396810 | Dog | 2016-08-22 16:13:00 | Injured | Raven | Spayed Female |
| A410668 | Cat | 2015-11-19 13:41:00 | Normal | Raven | Spayed Female |
- Sorting the names alphabetically gives 'Jewel', 'Raven', 'Sugar'.
- Since there is both a dog and a cat named 'Raven', the one whose protection started later (the dog) is shown first among them.
Therefore, running the SQL statement should produce the following result:
| ANIMAL_ID | NAME | DATETIME |
|---|---|---|
| A350276 | Jewel | 2017-08-13 13:50:00 |
| A396810 | Raven | 2016-08-22 16:13:00 |
| A410668 | Raven | 2015-11-19 13:41:00 |
| A349996 | Sugar | 2018-01-22 14:32:00 |
Retrieve ANIMAL_ID, NAME, and DATETIME for all animals. Show the results in ascending order of NAME, but for animals with the same name, show the most recently admitted animal first — that is, in descending order of DATETIME.
SQL
SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME, DATETIME DESC;