Normalize Case Before Sorting
Return sort_id and label from sorting_cases, ordered by LOWER(label) and then sort_id.
- String functions
- Sorting
Exercise brief
Understand the request
Customer data operations lead Imported labels arrive in mixed letter case, but the review list should group case variants together.
List employees sorted by last_name in a case-insensitive way, regardless of database collation. Use LOWER(). Show first_name, last_name.
Return
- Group labels without regard to letter case.
- Use sort_id to make equal normalized labels deterministic.
Constraints
- Use LOWER(label) as the primary sort expression.
- Do not rely on a database default collation.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A default collation can differ by database and deployment.
Hint 2
LOWER(label) creates a portable normalized key for these ASCII fixtures.
Hint 3
Add sort_id after LOWER(label) because several labels normalize to the same value.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT sort_id, label FROM sorting_cases ORDER BY LOWER(label), sort_id;Why this works
LOWER provides a deliberately portable case-normalized key for this ASCII exercise. Locale-aware production ordering should use a reviewed collation, but collation names and behavior are engine- and deployment-specific.
Success check
Alpha and beta case variants are adjacent and deterministically ordered.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| sort_id | label |
|---|---|
| 1 | alpha |
| 2 | Alpha |
| 6 | ALPHA |
| 3 | beta |
| 4 | Beta |
| 5 | gamma |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
LIMIT & OFFSET
Practice deterministic top-N, cutoff ties, offset pagination, composite keyset cursors, and resumable bounded batches.
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
Open the interactive workspace and practice across SQL topics.