For example, you can use it when you want to sort string values according to some criteria other than alphabetical order, such as by the hierarchical positions of job titles in a company. Introducing a CASE statement here can enhance your ORDER BY by allowing you to order results by some other (and multiple) criteria. Want a refresher on how it works? No problem! This article explains what an ORDER BY does. The ORDER BY clause is used to sort the result in either ascending or descending order. When Is a CASE Statement Used in an ORDER BY? Speaking of which, the main point of this article is to show you how the CASE statement works in an ORDER BY clause.
This means you don’t need to have CASE in SELECT if you just want to use it in an ORDER BY. You can use it multiple times in a single query. Using a CASE statement in a query once doesn’t mean you have hit your quota for using it. For example, you can use it in clauses like IN, WHERE, HAVING, and ORDER BY. Nope! Although it is most often used there, CASE is not limited to SELECT statements. If it’s not, then the value should be '21st-century film'. This statement means: when the column year is below (i.e., older than) 2001, then the value in the column century should be '20th-century film'. Then it uses a CASE statement to put values in a new column called century. This query selects all the columns from the table films. WHEN year < 2001 THEN '20th-century film' Now, say we want to write a query that, along with the columns from the table, shows an additional column with the century in which the title was filmed. Here’s the table films I’ll use in this example: id The most basic use of CASE is in a SELECT statement, so let’s start with an example. I think this syntax is best explained by how it works in an example. When used in a SELECT statement, it works like this: if it is the case when the condition is met, then return a certain value, or else return some other value, and end checking the conditions. It is SQL’s way of writing the IF-THEN-ELSE logic and consists of five keywords: CASE, WHEN, THEN, ELSE, and END. To put it very simply, it’s an SQL statement that goes through and returns values according to the conditions specified. If you want multiple knowledge sources, here’s an article explaining what CASE is. It contains almost 100 exercises and is focused on using CASE in practical SQL problems. If you want to practice using CASE statement, I recommend our interactive course Creating Basic SQL Reports. Then I’ll move to other uses of the CASE statement, especially in an ORDER BY clause. I’ll show and explain it to you with a short example. But have you ever used it in an ORDER BY clause? No? You will, once I show you how!ĭon’t worry if you’ve never used a CASE statement. Have you ever used a CASE statement? I’m sure you have, at least in a SELECT statement. This article will show you how and when to use CASE in an ORDER BY clause.
0 kommentar(er)
