CASE in ORDER BY in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the CASE statement do in an ORDER BY clause?

It allows you to customize the sorting order by specifying different conditions and corresponding sort priorities.

beginner

Write a simple example of using CASE in ORDER BY to sort by a column 'status' where 'urgent' comes first, then 'normal', then others.

ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'normal' THEN 2 ELSE 3 END

intermediate

Why use CASE in ORDER BY instead of just ordering by a column directly?

Because CASE lets you define a custom order that is not alphabetical or numerical, like prioritizing certain values over others.

intermediate

Can CASE in ORDER BY handle multiple conditions?

Yes, you can use multiple WHEN conditions to assign different sort priorities based on complex logic.

intermediate

What happens if no WHEN condition matches in a CASE inside ORDER BY?

The ELSE part defines the default sort priority; if ELSE is missing, NULL is used which may affect sorting.

What is the purpose of using CASE in an ORDER BY clause?

A To group rows by a condition

B To filter rows before sorting

C To create a custom sorting order based on conditions

D To rename columns in the result

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

Which keyword is used to specify the default sorting priority if no CASE condition matches?

A DEFAULT

B ELSE

C WHEN

D THEN

ELSE defines the default value when no WHEN condition matches in CASE.

How does the following ORDER BY clause sort rows? ORDER BY CASE priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 ELSE 3 END

A By numeric priority: high first, then medium, then others

B Alphabetically by priority

C Random order

D By length of the priority string

The CASE assigns numeric values to priorities to sort high first, then medium, then others.

Can CASE in ORDER BY be used to sort by multiple columns?

A No, only one column at a time

B Only if columns are text

C Only if columns are numeric

D Yes, by combining multiple CASE expressions

You can use multiple CASE expressions in ORDER BY to sort by multiple criteria.

If ELSE is omitted in CASE inside ORDER BY, what is the default behavior?

A Rows with unmatched conditions get NULL and sort accordingly

B Rows with unmatched conditions cause an error

C Rows with unmatched conditions are sorted last

D Rows with unmatched conditions are excluded

Without ELSE, unmatched conditions return NULL which affects sorting order.

Explain how to use CASE in ORDER BY to prioritize sorting of specific values in a column.

Think about assigning numbers to values to control their order.

Describe what happens when no WHEN condition matches in a CASE expression used in ORDER BY.

Consider what value CASE returns if no condition matches.