CASE in ORDER BY in SQL

Introduction

Use CASE in ORDER BY to sort data in a custom way based on conditions, not just simple column values.

You want to sort a list of products by category priority instead of alphabetically.

You need to order employees by their role importance, not by their names.

You want to show urgent tasks first, then normal tasks, then low priority tasks.

You want to sort data differently depending on a condition, like sorting by status with custom order.

Syntax

SQL

SELECT column1, column2
FROM table_name
ORDER BY CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE result_default
END;

The CASE expression inside ORDER BY lets you assign sorting values based on conditions.

The results from CASE are used to sort rows in the order of those results.

Examples

Sort products so Electronics come first, then Clothing, then others.

SQL

SELECT name, category
FROM products
ORDER BY CASE category
  WHEN 'Electronics' THEN 1
  WHEN 'Clothing' THEN 2
  ELSE 3
END;

Sort employees by role importance: Managers first, then Developers, then others.

SQL

SELECT employee_name, role
FROM employees
ORDER BY CASE
  WHEN role = 'Manager' THEN 1
  WHEN role = 'Developer' THEN 2
  ELSE 3
END;

Sample Program

This query sorts tasks by priority: High first, then Medium, then Low.

SQL

CREATE TABLE tasks (id INT, task_name VARCHAR(50), priority VARCHAR(10));
INSERT INTO tasks VALUES
(1, 'Fix bug', 'High'),
(2, 'Write docs', 'Low'),
(3, 'Code review', 'Medium'),
(4, 'Deploy', 'High');

SELECT task_name, priority
FROM tasks
ORDER BY CASE priority
  WHEN 'High' THEN 1
  WHEN 'Medium' THEN 2
  WHEN 'Low' THEN 3
  ELSE 4
END;

Important Notes

CASE in ORDER BY helps when sorting rules are not alphabetical or numeric but based on categories.

Always include ELSE in CASE to handle unexpected values and avoid NULL sorting issues.

Summary

CASE in ORDER BY lets you sort rows based on custom conditions.

It assigns sorting values to rows depending on conditions you set.

Use it to control the order of categories or priorities easily.