CASE WHEN in ORDER BY Clauses
There are many scenarios where you might need to sort query results in a way that is not strictly based on a single column's values. For instance, you may want to show certain types of records at the top of your results, such as prioritizing new releases, high-grossing movies, or specific genres. Relying on a simple ORDER BY clause does not always provide the flexibility you need. This is where the CASE WHEN expression in the ORDER BY clause becomes valuable, allowing you to define custom, dynamic sorting logic based on complex conditions.
123456789101112131415SELECT title, genre, rating, release_year, box_office FROM movies ORDER BY CASE WHEN release_year >= 2015 AND box_office > 500000000 THEN 1 -- Recent blockbusters WHEN rating >= 8.5 THEN 2 -- Highly rated movies ELSE 3 -- All others END, rating DESC;
In the query above, the CASE WHEN expression in the ORDER BY clause assigns a sorting priority to each movie. Movies released from 2015 onwards with a box office above 500 million are given the highest priority (1) and appear first. Next, movies with a rating of 8.5 or higher are grouped together (2), and all other movies follow (3). Within each group, movies are further sorted by their rating in descending order. This approach gives you fine-grained control over the display order, far beyond what a single column sort can achieve.
12345678910111213SELECT title, genre, rating, release_year FROM movies ORDER BY CASE WHEN genre = 'Animation' THEN rating WHEN genre = 'Action' THEN release_year ELSE box_office END DESC;
1. How does using CASE WHEN in an ORDER BY clause enhance the flexibility of your SQL queries?
2. Fill in the blanks to order movies so that comedies appear first, followed by dramas, then all other genres:
Obrigado pelo seu feedback!
Pergunte à IA
Pergunte à IA
Pergunte o que quiser ou experimente uma das perguntas sugeridas para iniciar nosso bate-papo
Incrível!
Completion taxa melhorada para 4.17CASE WHEN in ORDER BY Clauses
Deslize para mostrar o menu
There are many scenarios where you might need to sort query results in a way that is not strictly based on a single column's values. For instance, you may want to show certain types of records at the top of your results, such as prioritizing new releases, high-grossing movies, or specific genres. Relying on a simple ORDER BY clause does not always provide the flexibility you need. This is where the CASE WHEN expression in the ORDER BY clause becomes valuable, allowing you to define custom, dynamic sorting logic based on complex conditions.
123456789101112131415SELECT title, genre, rating, release_year, box_office FROM movies ORDER BY CASE WHEN release_year >= 2015 AND box_office > 500000000 THEN 1 -- Recent blockbusters WHEN rating >= 8.5 THEN 2 -- Highly rated movies ELSE 3 -- All others END, rating DESC;
In the query above, the CASE WHEN expression in the ORDER BY clause assigns a sorting priority to each movie. Movies released from 2015 onwards with a box office above 500 million are given the highest priority (1) and appear first. Next, movies with a rating of 8.5 or higher are grouped together (2), and all other movies follow (3). Within each group, movies are further sorted by their rating in descending order. This approach gives you fine-grained control over the display order, far beyond what a single column sort can achieve.
12345678910111213SELECT title, genre, rating, release_year FROM movies ORDER BY CASE WHEN genre = 'Animation' THEN rating WHEN genre = 'Action' THEN release_year ELSE box_office END DESC;
1. How does using CASE WHEN in an ORDER BY clause enhance the flexibility of your SQL queries?
2. Fill in the blanks to order movies so that comedies appear first, followed by dramas, then all other genres:
Obrigado pelo seu feedback!
