SQL Quiz: Multi Column Ordering
10 questions · exam conditions
0:00
Multi Column OrderingQuestion 1 of 10

An employee report must assign row number 1 to the highest-paid employee in each department. Salary ties must favor the employee hired earliest. If salary and hire date both tie, the employee with the smaller employee ID must win.

Which window expression correctly assigns the required row numbers?

ROW_NUMBER() OVER (ORDER BY salary DESC, hire_date ASC, employee_id ASC, department_id ASC)
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, hire_date DESC, employee_id ASC)
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date ASC, salary DESC, employee_id ASC)
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, hire_date ASC, employee_id ASC)
← Back to quizzes

SQL Quiz

SQL Quiz: Multi Column Ordering

Practice Multi Column Ordering in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Multi Column Ordering, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An employee report must assign row number 1 to the highest-paid employee in each department. Salary ties must favor the employee hired earliest. If salary and hire date both tie, the employee with the smaller employee ID must win.

Which window expression correctly assigns the required row numbers?

  1. ROW_NUMBER() OVER (ORDER BY salary DESC, hire_date ASC, employee_id ASC, department_id ASC)
  2. ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, hire_date DESC, employee_id ASC)
  3. ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date ASC, salary DESC, employee_id ASC)
  4. ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, hire_date ASC, employee_id ASC) (correct answer)
Explanation: When working with window functions that assign rankings within groups, your first instinct should be to check two things: whether PARTITION BY is used correctly, and whether the ORDER BY columns and their sort directions match the business rules exactly — in the right priority order. The requirement here has three layers: row 1 goes to the highest salary (descending), ties broken by earliest hire date (ascending), further ties broken by smallest employee ID (ascending) — and all of this must happen independently within each department. That last detail is critical: without PARTITION BY department_id, the ranking would run across the entire table rather than resetting per department. Option D captures everything precisely: it partitions by department, then orders by salary DESC, hire_date ASC, employee_id ASC, perfectly mirroring the stated rules. Option A fails immediately because it omits PARTITION BY department_id, meaning row number 1 would be assigned once globally to the highest-paid employee across all departments, not one per department. Option B uses PARTITION BY correctly but sorts hire_date DESC, which favors the most recently hired employee during a salary tie — the opposite of what's required. Option C has the right partition and correct directional logic for each column, but lists hire_date ASC before salary DESC, meaning it prioritizes hire date over salary entirely, assigning rank 1 to the earliest hire regardless of salary. A reliable tip: treat ORDER BY columns in window functions like a priority list — sequence matters as much as direction. Always map each business rule to a column-direction pair and verify they appear in the exact order specified.

Question 2

An invoice report is ordered primarily by amount DESC and secondarily by issued_at DESC. Invoice numbers can repeat in different branches, and branch IDs can each have many invoices. However, the combination of branch_id and invoice_number uniquely identifies every invoice.

Which ordering both preserves the two business priorities and guarantees a deterministic order for all remaining ties?

  1. ORDER BY amount DESC, issued_at DESC, invoice_number ASC
  2. ORDER BY amount DESC, issued_at DESC, branch_id ASC, invoice_number ASC (correct answer)
  3. ORDER BY amount DESC, issued_at DESC, branch_id ASC
  4. ORDER BY amount DESC, branch_id ASC, invoice_number ASC, issued_at DESC
Explanation: When designing an ORDER BY clause, you need to satisfy two distinct goals: preserving business priorities and ensuring a fully deterministic result. A deterministic order means that for every possible set of input rows, the database will always produce exactly the same sequence — no ties left unresolved. The passage tells you two critical facts: amount DESC and issued_at DESC are the business priorities (in that order), and the combination of branch_id + invoice_number uniquely identifies every invoice. That composite key is your tiebreaker — include both columns and you've guaranteed no two rows can occupy the same position. B is correct because it honors both business priorities first (amount DESC, issued_at DESC), then appends the full composite unique key (branch_id ASC, invoice_number ASC). Once all four columns are specified, no tie can survive — every row has a distinct, reproducible position. A fails because invoice_number alone is not unique (the passage explicitly states numbers repeat across branches). Two invoices from different branches can share the same amount, issued_at, and invoice_number, leaving the order non-deterministic. C fails for the same structural reason — branch_id alone isn't sufficient to break ties. Multiple invoices can share the same branch, so rows within a branch remain unresolved. D fails because it violates the business requirement: issued_at DESC must be the second sort priority, but here it's demoted to fourth place, after branch_id and invoice_number. The tiebreaker has overridden a business rule. As a general strategy: whenever a question asks about deterministic ordering, identify the table's unique key first, then confirm every column in that key appears after all business-priority columns.

Question 3

Products are shown as (product_id, price, rating): ('P10', 10, 5), ('P20', 10, 4), ('P30', 12, 5), ('P25', 12, 5), and ('P40', 12, 4). A query currently uses ORDER BY price ASC, rating DESC LIMIT 3. The intended third product is P25, and the existing price and rating priorities must be preserved.

Which replacement ordering guarantees that the first three products are P10, P20, and P25?

  1. ORDER BY rating DESC, price ASC, product_id ASC LIMIT 3
  2. ORDER BY price ASC, rating DESC, product_id DESC LIMIT 3
  3. ORDER BY price ASC, product_id ASC, rating DESC LIMIT 3
  4. ORDER BY price ASC, rating DESC, product_id ASC LIMIT 3 (correct answer)
Explanation: When SQL returns multiple rows with identical values in your sort columns, the database can return those tied rows in any order — this is called a non-deterministic tie. The fix is adding a tiebreaker column with unique values so every row has a guaranteed position. Here, sorting by price ASC, rating DESC produces this partial order: P10 (price=10, rating=5) → P20 (price=10, rating=4) → then a three-way tie among P30, P25, and P40 (all price=12, rating=5 or 4). P25 and P30 share both price=12 and rating=5, so without a tiebreaker, either could land in position 3. Adding product_id ASC resolves this: P25 comes before P30 alphabetically, locking P25 into the third slot. Answer D — ORDER BY price ASC, rating DESC, product_id ASC — achieves exactly this while preserving the original price and rating priorities. Answer A reorders the primary sort to rating DESC first, which would surface high-rated products regardless of price, breaking the original priority structure entirely. Answer B uses product_id DESC, which sorts alphabetically descending — P30 would appear before P25, putting P30 in position 3 instead of P25. Answer C inserts product_id ASC between price and rating, which changes the sort logic so that product ID takes priority over rating — a product with a lower ID but lower rating could outrank a higher-rated product at the same price, violating the stated requirement. The key study tip: whenever you need a deterministic LIMIT, always add a unique column (like a primary key) as the final tiebreaker — position matters; it must come last to avoid disrupting your intended business logic.

Question 4

A support dashboard must list Urgent tickets first, then Open tickets, and then Closed tickets. Within each status, the most recently updated ticket must appear first. Equal timestamps must be resolved by ascending ticket ID.

Which ORDER BY clause satisfies the dashboard requirements?

  1. ORDER BY CASE status WHEN 'Urgent' THEN 1 WHEN 'Open' THEN 2 WHEN 'Closed' THEN 3 END DESC, updated_at DESC, ticket_id ASC
  2. ORDER BY status ASC, updated_at DESC, ticket_id ASC
  3. ORDER BY CASE status WHEN 'Urgent' THEN 1 WHEN 'Open' THEN 2 WHEN 'Closed' THEN 3 END, updated_at DESC, ticket_id ASC (correct answer)
  4. ORDER BY updated_at DESC, CASE status WHEN 'Urgent' THEN 1 WHEN 'Open' THEN 2 WHEN 'Closed' THEN 3 END, ticket_id ASC
Explanation: When you need a custom sort order that doesn't match alphabetical or numerical ordering, a CASE expression inside ORDER BY is your tool. The idea is to map each category to a number, then sort by that number — giving you full control over sequence. The correct answer is C. It maps 'Urgent' → 1, 'Open' → 2, 'Closed' → 3, then sorts ascending by default (1 before 2 before 3), which produces exactly the priority order the dashboard requires. The secondary sort updated_at DESC surfaces the most recently updated tickets within each status, and ticket_id ASC serves as the tiebreaker when timestamps match. A adds DESC after the CASE expression, which reverses the numeric mapping — now 3 sorts first, meaning Closed tickets appear before Urgent ones. This is the trickiest distractor because the CASE logic looks correct at a glance. B uses status ASC, which sorts alphabetically: Closed → Open → Urgent. That's the opposite of the required order, since "C" comes before "O" which comes before "U" alphabetically. D places updated_at DESC as the primary sort key, which means a recently updated Closed ticket could appear before an older Urgent ticket. The status priority is completely undermined because it's no longer the first sort criterion. Study tip: Whenever a question asks you to enforce a non-alphabetical, non-numerical category order, reach for a CASE-to-integer mapping in ORDER BY, and double-check that you haven't accidentally added DESC to it unless you intend to reverse that mapping.

Question 5

A tasks query returns these rows, shown as (task_id, priority, due_date): (11, 3, '2026-05-04'), (12, 3, '2026-05-02'), (13, 3, '2026-05-02'), (14, 2, '2026-05-01'), and (15, 2, '2026-05-01').

In what order are the task IDs returned by ORDER BY priority DESC, due_date ASC, task_id DESC?

  1. 13, 12, 11, 15, 14 (correct answer)
  2. 11, 13, 12, 15, 14
  3. 13, 12, 11, 14, 15
  4. 15, 14, 13, 12, 11
Explanation: When SQL sorts with multiple ORDER BY columns, it works left to right: the first column is the primary sort, and each subsequent column only breaks ties among rows that share the same value in the previous column. Here, priority DESC sorts first. Tasks 11, 12, and 13 all have priority 3 (highest), so they come before tasks 14 and 15 (priority 2). Within the priority-3 group, due_date ASC applies: tasks 12 and 13 share 2026-05-02, which is earlier than task 11's 2026-05-04, so 12 and 13 come first. Task 11 follows. Now, tasks 12 and 13 are still tied on both priority and due_date, so task_id DESC breaks the tie — 13 comes before 12. Within the priority-2 group, tasks 14 and 15 share the same due_date 2026-05-01, so again task_id DESC applies: 15 before 14. Final order: 13, 12, 11, 15, 14 — confirming answer A. Answer B (11, 13, 12, 15, 14) ignores due_date ASC, placing task 11 first within the priority-3 group instead of last. Answer C (13, 12, 11, 14, 15) gets the priority-3 group right but applies task_id ASC instead of task_id DESC for the priority-2 group, producing 14 before 15. Answer D (15, 14, 13, 12, 11) reverses everything, as if all three columns were DESC. A helpful habit: mentally group rows by the first sort column, then re-sort each group by the second, and so on — cascading inward until all ties are broken.

Question 6

An accounts query returns these rows, shown as (account_id, balance): (101, -500), (102, 500), (103, -500), and (104, 300).

In what account ID order are the rows returned by ORDER BY ABS(balance) DESC, balance ASC, account_id DESC?

  1. 102, 103, 101, 104
  2. 103, 101, 102, 104 (correct answer)
  3. 101, 103, 102, 104
  4. 104, 103, 101, 102
Explanation: When SQL sorts by multiple columns, each column acts as a tiebreaker for the previous one — so you must work left to right, applying the next criterion only when values are equal. Start by computing ABS(balance) for each row: account 101 → 500, account 102 → 500, account 103 → 500, account 104 → 300. Sorting by ABS(balance) DESC puts the three 500-absolute-value accounts first, then account 104 last. That immediately eliminates D. Now you need to break the three-way tie among accounts 101, 102, and 103 using balance ASC. Their actual balances are: 101 → -500, 102 → +500, 103 → -500. Ascending order puts the negatives first: -500 before +500. So accounts 101 and 103 come before 102. That leaves one final tie to break — between accounts 101 and 103, which both have balance = -500. The third criterion, account_id DESC, sorts them in descending ID order: 103 before 101. The final order is 103, 101, 102, 104 — confirming B. Choice A (102, 103, 101, 104) misapplies the balance ASC step, placing the positive balance first instead of last. Choice C (101, 103, 102, 104) gets the overall grouping right but applies account_id ASC instead of DESC for the final tiebreak. Choice D (104, ...) sorts ABS(balance) in the wrong direction, putting the smallest absolute value first. Study tip: Whenever you see multi-column ORDER BY, trace each column's effect one at a time — only move to the next column when the current one produces a tie.

Question 7

A customer report must sort customers by last name alphabetically. Customers with the same last name must be listed from newest to oldest signup date. If both values tie, the smaller customer ID must appear first.

Which ORDER BY clause implements all three requirements?

  1. ORDER BY last_name ASC, signup_date DESC, customer_id ASC (correct answer)
  2. ORDER BY last_name ASC, signup_date ASC, customer_id ASC
  3. ORDER BY last_name ASC, customer_id ASC, signup_date DESC
  4. ORDER BY last_name DESC, signup_date DESC, customer_id ASC
Explanation: When you see a multi-column ORDER BY question, think of it as a priority list: SQL sorts by the first column, then breaks ties using the second column, then the third. The order of those columns matters just as much as the direction (ASC vs. DESC) assigned to each one. The requirements translate directly into three sorting keys: last name alphabetically (ASC), newest-to-oldest signup date (DESC, since larger/more recent dates come first when sorted descending), and smallest customer ID first (ASC) as the final tiebreaker. Option A — ORDER BY last_name ASC, signup_date DESC, customer_id ASC — maps perfectly to all three requirements in the correct sequence and direction. Option B uses signup_date ASC, which sorts oldest-to-newest — the exact opposite of what the report needs. Customers who signed up years ago would appear before recent ones, violating the "newest to oldest" rule. Option C places customer_id ASC before signup_date DESC, swapping the second and third priorities. This means ties in last name are broken by customer ID first, completely ignoring signup date until IDs also tie — which inverts the intended logic. Option D uses last_name DESC, sorting last names in reverse alphabetical order (Z to A), which fails the very first requirement. A useful strategy: underline each sorting rule in the problem and write out the column, then its direction, before looking at the choices. Mistakes in multi-sort questions almost always come from misreading ASC/DESC or from columns appearing in the wrong order — treat both as equally important when evaluating your answer.

Question 8

After grouping sales by category, the results are: Alpha has total sales 100 from 2 sales; Beta has total sales 100 from 3 sales; Delta has total sales 100 from 3 sales; and Gamma has total sales 90 from 5 sales.

What category sequence results from ORDER BY SUM(amount) DESC, COUNT(*) DESC, category ASC?

  1. Gamma, Beta, Delta, Alpha
  2. Alpha, Beta, Delta, Gamma
  3. Beta, Delta, Alpha, Gamma (correct answer)
  4. Delta, Beta, Alpha, Gamma
Explanation: When SQL sorts by multiple columns, each column acts as a tiebreaker for the one before it. Here, the query sorts first by SUM(amount) DESC, then by COUNT(*) DESC, then by category ASC. Start with the primary sort: Alpha, Beta, and Delta all have SUM(amount) = 100, so they're tied at the top. Gamma has SUM(amount) = 90, so it drops to last place regardless of any other criteria. Now apply the second sort (COUNT(*) DESC) to break the three-way tie. Beta and Delta both have COUNT(*) = 3, so they come before Alpha (which has COUNT(*) = 2). Beta and Delta are still tied with each other. Finally, apply the third sort (category ASC) to break that remaining tie. Alphabetically, "Beta" comes before "Delta," so Beta leads Delta. The resulting order is Beta, Delta, Alpha, Gamma — which is answer C. Choice A (Gamma, Beta, Delta, Alpha) is wrong because it places Gamma first, but Gamma has the lowest total sales and should appear last. Choice B (Alpha, Beta, Delta, Gamma) ignores the COUNT(*) DESC tiebreaker entirely — Alpha has fewer sales transactions than Beta and Delta, so it shouldn't precede them. Choice D (Delta, Beta, Alpha, Gamma) fails on the third tiebreaker: when COUNT(*) is equal, the sort is category ASC, meaning Beta (B) must come before Delta (D), not after. When you see multi-column ORDER BY, mentally apply each sort in sequence, treating each as a tiebreaker that only activates when all previous columns are equal.

Question 9

A query begins with SELECT last_name, first_name, hire_date FROM employees and ends with ORDER BY 3 DESC, 1 ASC, 2 DESC.

Which description correctly states how the result is ordered?

  1. Newest hire date first; tied dates use last name ascending, then first name descending. (correct answer)
  2. Newest hire date first; tied dates use first name ascending, then last name descending.
  3. Last name descending first; tied names use first name ascending, then hire date descending.
  4. Hire date ascending first; tied dates use last name ascending, then first name descending.
Explanation: When you see ORDER BY with numbers instead of column names, those numbers refer to the position of the columns in your SELECT list. Here, SELECT last_name, first_name, hire_date means position 1 = last_name, position 2 = first_name, and position 3 = hire_date. So ORDER BY 3 DESC, 1 ASC, 2 DESC translates to: sort by hire_date descending first, then by last_name ascending for ties, then by first_name descending for remaining ties. This maps perfectly to choice A — newest hire dates appear first (DESC on a date means latest first), ties are broken by last name alphabetically ascending, and further ties by first name descending. That's exactly right. Choice B is wrong because it swaps the tiebreaker order — it claims first name comes before last name, but position 1 (last_name) is listed before position 2 (first_name) in the ORDER BY clause. Choice C is wrong on the primary sort entirely — it says last name sorts first, but position 3 (hire_date) is the first sort key, not position 1. Choice D is wrong because it reverses the hire date direction. DESC on a date gives you newest first (descending = largest value first), not oldest first. Ascending would show the oldest hires at the top. Study tip: When you see numeric aliases in ORDER BY, immediately map each number back to its column in the SELECT list before reading the question — it prevents the most common trap of misidentifying which column is being sorted.

Question 10

A query returns these rows, shown as (region, revenue, salesperson): ('North', 90, 'Zoe'), ('North', 100, 'Amy'), ('South', 100, 'Bob'), and ('South', 100, 'Ana').

What is the result order for ORDER BY region, revenue DESC, salesperson?

  1. North/Amy, North/Zoe, South/Ana, South/Bob (correct answer)
  2. North/Zoe, North/Amy, South/Ana, South/Bob
  3. South/Ana, South/Bob, North/Amy, North/Zoe
  4. North/Amy, North/Zoe, South/Bob, South/Ana
Explanation: When you see ORDER BY with multiple columns, think of it as a tiebreaker system: SQL sorts by the first column, then uses the second column only when the first produces ties, and so on. Each column can independently be ASC (default) or DESC. Here, ORDER BY region, revenue DESC, salesperson means: sort by region ascending first, then by revenue descending within each region, then by salesperson ascending to break any remaining ties. Starting with region ascending gives you all North rows before South rows. Within North, you have Zoe (90) and Amy (100). Since revenue is DESC, the higher revenue comes first — Amy (100) before Zoe (90). Within South, both Bob and Ana have revenue 100, so they're tied on revenue. The final tiebreaker, salesperson ascending alphabetically, puts Ana before Bob. The final order is Amy, Zoe, Ana, Bob — confirming A is correct. Choice B gets North wrong: it places Zoe before Amy, which would be revenue ASC, not DESC. Choice C reverses the region order entirely, as if region were DESC rather than the default ASC. Choice D gets South wrong: it puts Bob before Ana, ignoring the alphabetical salesperson tiebreaker (or mistakenly applying DESC there). A good strategy: when you encounter a multi-column ORDER BY, mentally process one column at a time, left to right. Only move to the next column when you have a tie in the current one — and always check whether each column is ASC or DESC independently.