CASE in ORDER BY in SQL - Mini Project: Build & Apply

Sorting Products by Category Priority Using CASE in ORDER BY

📖 Scenario: You manage a product database for an online store. You want to display products sorted by category priority: Electronics first, then Clothing, then Home, and finally all other categories.

🎯 Goal: Create a SQL query that sorts products by category priority using CASE inside the ORDER BY clause.

📋 What You'll Learn

Create a table called products with columns id, name, and category.

Insert exactly these products: (1, 'Laptop', 'Electronics'), (2, 'T-Shirt', 'Clothing'), (3, 'Vacuum', 'Home'), (4, 'Smartphone', 'Electronics'), (5, 'Jeans', 'Clothing').

Write a SELECT query to get name and category from products.

Use CASE inside ORDER BY to sort categories in this order: Electronics, Clothing, Home, then others alphabetically.

💡 Why This Matters

🌍 Real World

Sorting products by priority helps online stores show important categories first, improving user experience.

💼 Career

Knowing how to use CASE in ORDER BY is useful for database querying and reporting tasks in many jobs.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (text), and category (text). Then insert these exact rows: (1, 'Laptop', 'Electronics'), (2, 'T-Shirt', 'Clothing'), (3, 'Vacuum', 'Home'), (4, 'Smartphone', 'Electronics'), (5, 'Jeans', 'Clothing').

SQL

-- Create table products and insert rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the products.

2

Write a basic SELECT query for product names and categories

Write a SELECT query to get the name and category columns from the products table.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT
);

INSERT INTO products (id, name, category) VALUES
  (1, 'Laptop', 'Electronics'),
  (2, 'T-Shirt', 'Clothing'),
  (3, 'Vacuum', 'Home'),
  (4, 'Smartphone', 'Electronics'),
  (5, 'Jeans', 'Clothing');

-- Write SELECT query to get name and category
-- Your code here

Hint

Use SELECT name, category FROM products; to get the product names and categories.

3

Add CASE in ORDER BY to sort by category priority

Modify the SELECT query to sort the results by category priority using CASE inside ORDER BY. Sort categories in this order: 'Electronics' first, then 'Clothing', then 'Home', and then all other categories alphabetically.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT
);

INSERT INTO products (id, name, category) VALUES
  (1, 'Laptop', 'Electronics'),
  (2, 'T-Shirt', 'Clothing'),
  (3, 'Vacuum', 'Home'),
  (4, 'Smartphone', 'Electronics'),
  (5, 'Jeans', 'Clothing');

-- Add ORDER BY with CASE to sort by category priority
-- Your code here

Hint

Use CASE category WHEN 'Electronics' THEN 1 WHEN 'Clothing' THEN 2 WHEN 'Home' THEN 3 ELSE 4 END inside ORDER BY to set priority.

4

Complete the query with final sorting and formatting

Ensure the final query selects name and category from products and sorts by category priority using CASE in ORDER BY, then by category alphabetically for others.

SQL

CREATE TABLE products (
  id INTEGER,
  name TEXT,
  category TEXT
);

INSERT INTO products (id, name, category) VALUES
  (1, 'Laptop', 'Electronics'),
  (2, 'T-Shirt', 'Clothing'),
  (3, 'Vacuum', 'Home'),
  (4, 'Smartphone', 'Electronics'),
  (5, 'Jeans', 'Clothing');

-- Finalize the query with ORDER BY using CASE and category
-- Your code here

Hint

Make sure the query includes the full SELECT statement with the ORDER BY CASE sorting logic.

Practice

1. What does the CASE statement do when used inside an ORDER BY clause in SQL?

easy

A. It filters rows before sorting them.

B. It allows sorting rows based on custom conditions you define.

C. It changes the data type of the sorted column.

D. It groups rows before sorting.

Solution

1. Step 1: Understand the role of CASE in ORDER BY

   The CASE statement inside ORDER BY assigns sorting values conditionally to each row.

2. Step 2: Differentiate from filtering or grouping

   CASE does not filter or group rows; it only affects sorting order.

3. Final Answer:

   It allows sorting rows based on custom conditions you define. -> Option B

4. Quick Check:

   CASE in ORDER BY = custom sorting [OK]

Hint: CASE in ORDER BY controls sorting order by conditions [OK]

Common Mistakes:

• Thinking CASE filters rows
• Assuming CASE changes data types
• Confusing CASE with GROUP BY

2. Which of the following is the correct syntax to use CASE inside an ORDER BY clause?

easy

A. ORDER BY CASE WHEN condition THEN value ELSE value END

B. ORDER BY IF condition THEN value ELSE value END

C. ORDER BY SWITCH condition THEN value ELSE value END

D. ORDER BY SELECT CASE condition THEN value ELSE value END

Solution

1. Step 1: Recall correct CASE syntax in ORDER BY

   The correct syntax uses CASE WHEN ... THEN ... ELSE ... END without extra keywords.

2. Step 2: Identify incorrect keywords

   Options A, B, and C use invalid keywords like SELECT, IF, or SWITCH which are not valid in ORDER BY CASE.

3. Final Answer:

   ORDER BY CASE WHEN condition THEN value ELSE value END -> Option A

4. Quick Check:

   CASE syntax = WHEN THEN ELSE END [OK]

Hint: Use CASE WHEN ... THEN ... ELSE ... END in ORDER BY [OK]

Common Mistakes:

• Using IF instead of CASE
• Adding SELECT inside ORDER BY
• Using SWITCH keyword

3. Given the table Tasks with column priority having values 'High', 'Medium', 'Low', what will the query below return?

SELECT priority FROM Tasks ORDER BY CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END;

medium

A. Rows sorted with 'High' first, then 'Medium', then 'Low'.

B. Rows sorted alphabetically by priority.

C. Rows sorted with 'Low' first, then 'Medium', then 'High'.

D. Syntax error due to CASE in ORDER BY.

Solution

1. Step 1: Understand CASE assigns sorting values

   CASE assigns 1 to 'High', 2 to 'Medium', and 3 to others (like 'Low').

2. Step 2: ORDER BY sorts ascending by these values

   Rows with priority 'High' come first, then 'Medium', then 'Low'.

3. Final Answer:

   Rows sorted with 'High' first, then 'Medium', then 'Low'. -> Option A

4. Quick Check:

   CASE assigns order numbers = sorted by priority [OK]

Hint: CASE assigns numbers to priorities to sort custom order [OK]

Common Mistakes:

• Assuming alphabetical sort
• Thinking CASE causes syntax error
• Confusing order direction

4. Identify the error in this SQL query:

SELECT name FROM Employees ORDER BY CASE WHEN department = 'Sales' THEN 1 WHEN department = 'HR' THEN 2 ELSE 3;

medium

A. ORDER BY cannot use CASE statements.

B. Incorrect use of ELSE in CASE.

C. Missing END keyword to close CASE statement.

D. Missing THEN keyword after conditions.

Solution

1. Step 1: Check CASE syntax completeness

   The CASE statement must end with the keyword END to close it.

2. Step 2: Identify missing END keyword

   The query lacks END after ELSE 3, causing syntax error.

3. Final Answer:

   Missing END keyword to close CASE statement. -> Option C

4. Quick Check:

   CASE must end with END [OK]

Hint: Always close CASE with END keyword [OK]

Common Mistakes:

• Omitting END keyword
• Misplacing ELSE clause
• Thinking CASE not allowed in ORDER BY

5. You want to sort products so that 'Electronics' appear first, 'Clothing' second, and all others last. Which ORDER BY clause using CASE achieves this?

hard

A. ORDER BY CASE category WHEN 'Clothing' THEN 1 WHEN 'Electronics' THEN 2 ELSE 3 END

B. ORDER BY CASE WHEN category = 'Electronics' THEN 3 WHEN category = 'Clothing' THEN 2 ELSE 1 END

C. ORDER BY category = 'Electronics' DESC, category = 'Clothing' DESC

D. ORDER BY CASE category WHEN 'Electronics' THEN 1 WHEN 'Clothing' THEN 2 ELSE 3 END

Solution

1. Step 1: Assign lowest number to 'Electronics' for first position

   ORDER BY CASE category WHEN 'Electronics' THEN 1 WHEN 'Clothing' THEN 2 ELSE 3 END assigns 1 to 'Electronics', 2 to 'Clothing', and 3 to others, sorting correctly.

2. Step 2: Check other options for order correctness

   ORDER BY CASE WHEN category = 'Electronics' THEN 3 WHEN category = 'Clothing' THEN 2 ELSE 1 END reverses order numbers, ORDER BY CASE category WHEN 'Clothing' THEN 1 WHEN 'Electronics' THEN 2 ELSE 3 END swaps categories, ORDER BY category = 'Electronics' DESC, category = 'Clothing' DESC uses boolean sorting which may not guarantee order.

3. Final Answer:

   ORDER BY CASE category WHEN 'Electronics' THEN 1 WHEN 'Clothing' THEN 2 ELSE 3 END -> Option D

4. Quick Check:

   Lowest number = highest priority in CASE [OK]

Hint: Assign 1 to highest priority category in CASE [OK]

Common Mistakes:

• Swapping priority numbers
• Using boolean expressions without CASE
• Forgetting ELSE clause