Sorting query results: can SQL ORDER BY sort by multiple columns (primary key, tie-breakers), or is it limited to only one column?

Difficulty: Easy

Correct Answer: Incorrect

Explanation:


Introduction / Context:
Sorting is fundamental in reporting and pagination. This question evaluates whether ORDER BY can handle multiple sort keys (e.g., last_name, first_name) or if it is limited to a single column.



Given Data / Assumptions:

  • Standard SQL ORDER BY syntax is available.
  • Columns may be named or referenced by ordinal or expression.
  • We can specify ASC/DESC independently per key.


Concept / Approach:
SQL ORDER BY supports a comma-separated list of expressions, each with its own direction. For instance, ... ORDER BY last_name ASC, first_name ASC; sorts primarily by last_name and uses first_name to break ties. This generalizes to any number of keys, including expressions (e.g., ORDER BY COALESCE(priority, 999), created_at DESC).



Step-by-Step Solution:

Identify the need for stable tie-breaking across rows with equal primary sort values.Use multiple ORDER BY items separated by commas.Assign directions (ASC/DESC) per item as required.Test with sample data to verify expected hierarchy of ordering.


Verification / Alternative check:
Run an example and compare: ORDER BY department, salary DESC clearly sorts first by department, then within each department by salary descending.



Why Other Options Are Wrong:

  • “Correct” conflicts with the multi-key capability of ORDER BY.
  • Limiting to numeric columns is false; text, dates, and expressions are allowed.
  • ORDER BY does not depend on indexes to function, though indexes can affect performance.
  • SORTING does not require GROUP BY.


Common Pitfalls:
Relying on implicit sort order or assuming rows are returned in insertion order. Always specify ORDER BY explicitly for deterministic presentation.



Final Answer:
Incorrect

More Questions from Introduction to SQL

Discussion & Comments

No comments yet. Be the first to comment!
Join Discussion