This is my second Programmers SQL problem.
Today’s Problem
Popular Ice Cream — result: success.
The problem asks you to sort ice cream flavors (FLAVOR) from the FIRST_HALF table in descending order of total orders (TOTAL_ORDER), and when the total orders are equal, in ascending order of shipment ID (SHIPMENT_ID).
Here’s the answer I submitted:
SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID ASC
What I Learned
Sorting isn’t sort, it’s ORDER BY. When I realized I needed to sort, the first word that came to mind was sort. The name I was used to from programming languages came up first. In SQL it’s ORDER BY, and fortunately I remembered that correctly.
You can chain multiple sort conditions with commas. I only knew conceptually that you could sort again by a different criterion when values are equal, but this time I was able to use it right away. The earlier condition takes priority, and the later condition only applies when values from the earlier one are equal.
I also confirmed this time that you can attach DESC and ASC separately to each condition. You can mix them — one descending, one ascending.
For example, suppose there are rows with the same total order count:
| SHIPMENT_ID | FLAVOR | TOTAL_ORDER |
|---|---|---|
| 3 | Chocolate | 100 |
| 1 | Strawberry | 100 |
| 5 | Vanilla | 80 |
With just TOTAL_ORDER DESC, it’s undetermined whether Chocolate or Strawberry comes first. Adding SHIPMENT_ID ASC afterward fixes the order to Strawberry(1) → Chocolate(3). The second condition isn’t there to make the order look nice — its role is to make the order deterministic.
Retrospective
This problem was easy, so there’s not much more to reflect on. In the first problem, I ran into a syntax error because I didn’t know the clause order. This time I passed in one go without getting stuck anywhere.
If I had to pick one thing, it’s that I confirmed the opposite side of knowing a concept vs. being able to use it. I only knew multi-column sorting conceptually, but this time my hands moved right to it.
Things to Study Further
- When does
ORDER BYactually execute? — In the first post, I left as homework the fact that write order and execution order differ. I should check whereORDER BYfalls in that execution order. Checking whether aliases defined withASinSELECTcan be used inORDER BYmight give the answer too. - Order of rows with equal sort values — In the example above, I wrote that the order is “undetermined” if you don’t add the second condition. I need to actually check what order the DB gives in practice, and whether the same order is guaranteed every time (MySQL ORDER BY Optimization).
- Where do
NULLs go when mixed in? — If the sorted column containsNULL, does it go first or last, and does this differ by DB? - Using it together with
LIMIT— Problems like “only the top N” probably need to cut the count after sorting. I need to check that syntax. - Using column numbers in
ORDER BY— I heard you can use ordinal numbers instead of column names, likeORDER BY 1, 2. I need to check whether this works, and whether it’s recommended.