ORDER BY clause in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Sorting a range by a single column

You have a table in range A1:B5 with names in column A and scores in column B. Which formula correctly sorts the table by scores in ascending order?

Google Sheets

A1:B5

Data:
A1: Name, B1: Score
A2: Alice, B2: 85
A3: Bob, B3: 92
A4: Carol, B4: 78
A5: Dave, B5: 90

A =SORT(A2:B5, 2, TRUE)

B =SORT(A2:B5, 1, FALSE)

C =SORT(A2:B5, 2, FALSE)

D =SORT(A2:B5, 1, TRUE)

💡 Hint

Remember, the second argument in SORT is the column index to sort by, and TRUE means ascending order.

The formula =SORT(A2:B5, 2, TRUE) sorts the range by the second column (scores) in ascending order. Other options either sort by the wrong column or in descending order.

📊 Formula Result

intermediate

Sorting by multiple columns

Given a table in A1:C6 with columns Name, Department, and Salary, which formula sorts first by Department ascending, then by Salary descending?

Google Sheets

A1:C6

Data:
A1: Name, B1: Department, C1: Salary
A2: John, B2: Sales, C2: 50000
A3: Jane, B3: HR, C3: 60000
A4: Mike, B4: Sales, C4: 55000
A5: Anna, B5: HR, C5: 58000
A6: Tom, B6: IT, C6: 62000

A =SORT(A2:C6, 2, TRUE, 3, FALSE)

B =SORT(A2:C6, {3,2}, {FALSE, TRUE})

C =SORT(A2:C6, {2,3}, {TRUE, FALSE})

D =SORT(A2:C6, 2, FALSE, 3, TRUE)

💡 Hint

Use arrays for columns and sort orders to sort by multiple columns.

Option C correctly sorts by column 2 (Department) ascending, then column 3 (Salary) descending. Options A and D have wrong syntax, and B reverses the order.

❓ Function Choice

advanced

Choosing the right function for sorting with conditions

You want to sort a list of products by price ascending but only include products with stock greater than 10. Which formula achieves this?

Google Sheets

A1:C6

Data:
A1: Product, B1: Price, C1: Stock
A2: Pen, B2: 1.5, C2: 5
A3: Notebook, B3: 3.0, C3: 15
A4: Eraser, B4: 0.5, C4: 20
A5: Pencil, B5: 1.0, C5: 8
A6: Marker, B6: 2.0, C6: 12

A =FILTER(A2:C6, SORT(C2:C6, 1, TRUE)>10)

B =SORT(FILTER(A2:C6, C2:C6>10), 2, TRUE)

C =SORT(A2:C6, 2, TRUE, C2:C6>10)

D =FILTER(SORT(A2:C6, 2, TRUE), C2:C6>10)

💡 Hint

Filter first, then sort the filtered data.

Option B correctly filters rows where stock > 10, then sorts by price ascending. Option B filters after sorting, which keeps all rows and then filters incorrectly. Options A and C have invalid syntax or logic.

❓ data_analysis

advanced

Analyzing output of SORT with mixed data types

Given the range A1:B5 with names and mixed numeric/text scores, what is the output of =SORT(A2:B5, 2, TRUE)?

Google Sheets

A1:B5

Data:
A1: Name, B1: Score
A2: Alice, B2: 85
A3: Bob, B3: "N/A"
A4: Carol, B4: 78
A5: Dave, B5: 90

A [[Alice, 85], [Carol, 78], [Dave, 90], [Bob, "N/A"]]

B [[Bob, "N/A"], [Carol, 78], [Alice, 85], [Dave, 90]]

C Error: Cannot sort range with mixed data types

D [[Carol, 78], [Alice, 85], [Dave, 90], [Bob, "N/A"]]

💡 Hint

Text values are sorted after numbers when sorting ascending.

SORT places numbers before text when sorting ascending. So rows with numeric scores come first sorted ascending, then rows with text values like "N/A" come last.

🎯 Scenario

expert

Complex sorting with dynamic range and multiple criteria

You have a dynamic list of employees in A1:D (Name, Department, Age, Salary). You want a formula that sorts all employees by Department ascending, then by Salary descending, but only includes employees older than 30. Which formula correctly does this?

Google Sheets

Data example (starting at A1):
Name | Department | Age | Salary
John | Sales      | 28  | 50000
Jane | HR         | 35  | 60000
Mike | Sales      | 40  | 55000
Anna | HR         | 32  | 58000
Tom  | IT         | 45  | 62000

Note: The list can grow or shrink.

A =SORT(FILTER(A2:D, C2:C>30), {2,4}, {TRUE, FALSE})

B =FILTER(SORT(A2:D, {2,4}, {TRUE, FALSE}), C2:C>30)

C =SORT(FILTER(A2:D, C2:C>=30), 2, TRUE, 4, FALSE)

D =SORT(FILTER(A2:D, C2:C>30), 2, TRUE, 4, FALSE)

💡 Hint

Filter first by age > 30, then sort by Department ascending and Salary descending using arrays.

Option A correctly filters employees older than 30, then sorts by Department ascending (column 2) and Salary descending (column 4). Option A filters after sorting, which includes all rows before filtering. Options C and D have incorrect SORT syntax for multiple columns.

Practice

1. What does the ORDER BY clause do inside a Google Sheets QUERY function?

easy

A. Filters rows based on a condition

B. Sorts the data by a specified column

C. Adds new columns to the data

D. Deletes duplicate rows

Solution

1. Step 1: Understand the purpose of ORDER BY

   The ORDER BY clause is used to sort data in a query result.

2. Step 2: Identify what sorting means

   Sorting arranges rows based on values in a chosen column, either ascending or descending.

3. Final Answer:

   Sorts the data by a specified column -> Option B

4. Quick Check:

   ORDER BY = Sort data [OK]

Hint: ORDER BY always sorts data by column [OK]

Common Mistakes:

• Thinking ORDER BY filters rows
• Confusing ORDER BY with adding columns
• Assuming ORDER BY removes duplicates

2. Which of the following is the correct syntax to sort data by column B in ascending order using QUERY in Google Sheets?

easy

A. =QUERY(A1:C10, "SELECT * ORDER BY B DESC")

B. =QUERY(A1:C10, "SELECT * ORDER BY B DOWN")

C. =QUERY(A1:C10, "SELECT * ORDER BY B ASC")

D. =QUERY(A1:C10, "SELECT * ORDER BY B UP")

Solution

1. Step 1: Recall correct ORDER BY syntax

   In QUERY, ascending order is specified by ASC, descending by DESC.

2. Step 2: Check each option

   =QUERY(A1:C10, "SELECT * ORDER BY B ASC") uses ORDER BY B ASC which is correct syntax for ascending order. Options B and C use invalid keywords DOWN and UP. =QUERY(A1:C10, "SELECT * ORDER BY B DESC") sorts descending, not ascending.

3. Final Answer:

   =QUERY(A1:C10, "SELECT * ORDER BY B ASC") -> Option C

4. Quick Check:

   ASC means ascending order [OK]

Hint: Use ASC for ascending, DESC for descending [OK]

Common Mistakes:

• Using DOWN or UP instead of ASC or DESC
• Mixing ascending and descending keywords
• Forgetting quotes around the query string

3. Given the data in A1:B4:

A    Score
John  85
Anna  92
Mike  78
Sara  90

What will be the result of =QUERY(A1:B4, "SELECT A, B ORDER BY B DESC")?

medium

A. John 85Sara 90Anna 92Mike 78

B. Sara 90Anna 92John 85Mike 78

C. Mike 78John 85Sara 90Anna 92

D. Anna 92Sara 90John 85Mike 78

Solution

1. Step 1: Understand ORDER BY B DESC

   The query sorts rows by column B (Score) in descending order, highest to lowest.

2. Step 2: Sort the scores

   Scores sorted descending: 92 (Anna), 90 (Sara), 85 (John), 78 (Mike).

3. Final Answer:

   Anna 92
   Sara 90
   John 85
   Mike 78 -> Option D

4. Quick Check:

   DESC sorts highest to lowest [OK]

Hint: DESC sorts from highest to lowest [OK]

Common Mistakes:

• Confusing ascending and descending order
• Not sorting by the correct column
• Ignoring the header row in output

4. You wrote this formula: =QUERY(A1:C10, "SELECT A, B ORDER BY C DESCENDING") but it gives an error. What is the problem?

medium

A. The keyword should be DESC, not DESCENDING

B. You cannot order by column C

C. The SELECT clause must include column C

D. The query string must be lowercase

Solution

1. Step 1: Check ORDER BY syntax

   The correct keyword for descending order is DESC, not DESCENDING.

2. Step 2: Validate other parts

   Ordering by column C is allowed even if not selected. Query strings are case-insensitive, so lowercase is fine but not required.

3. Final Answer:

   The keyword should be DESC, not DESCENDING -> Option A

4. Quick Check:

   Use DESC for descending order [OK]

Hint: Use DESC, not DESCENDING for descending order [OK]

Common Mistakes:

• Typing DESCENDING instead of DESC
• Thinking ORDER BY column must be selected
• Assuming query strings must be lowercase

5. You have a table with columns: Name, Age, and Score. You want to list all names and scores sorted by Age ascending, then Score descending. Which QUERY formula is correct?

hard

A. =QUERY(A1:C10, "SELECT A, C ORDER BY B ASC, C DESC")

B. =QUERY(A1:C10, "SELECT A, C ORDER BY C DESC, B ASC")

C. =QUERY(A1:C10, "SELECT A, B ORDER BY B ASC, C DESC")

D. =QUERY(A1:C10, "SELECT A, C ORDER BY B DESC, C ASC")

Solution

1. Step 1: Identify columns and order

   We want to select Name (A) and Score (C). Sort first by Age (B) ascending, then Score (C) descending.

2. Step 2: Match query syntax

   =QUERY(A1:C10, "SELECT A, C ORDER BY B ASC, C DESC") selects A and C, orders by B ASC (Age ascending), then C DESC (Score descending). Other options either select wrong columns or order incorrectly.

3. Final Answer:

   =QUERY(A1:C10, "SELECT A, C ORDER BY B ASC, C DESC") -> Option A

4. Quick Check:

   ORDER BY multiple columns works left to right [OK]

Hint: Order by multiple columns separated by commas [OK]

Common Mistakes:

• Selecting wrong columns in SELECT clause
• Reversing order directions
• Ordering by columns not selected