While using ORDER BY is recommended, when you want to mutate a column’s value while not the other values related to the said column, MAX() and MIN() can help you with that. Given the following list:Ĭreating your sorting algorithm using SQL. We run the following query: select points, CASE WHEN MAX ( points ) > 60 then 'VIP CUSTOMER' WHEN MAX ( points ) > 40 then 'Gold CUSTOMER' WHEN MAX ( points ) > 20 then 'Silver CUSTOMER' ELSE 'REGULAR CUSTOMER' END as customer_royalty from customer_points īecause the customer has accumulated a maximum of 97 points within a one-month period, they are considered a VIP CUSTOMER.Īside from the usuals, we also have more examples wherein we can utilize the CASE WHEN MAX combo. Moreover, we also add conditionals determining if they are a GOLD customer ( MAX points > 40), a silver customer ( MAX points > 20), or just a regular customer. You would want to determine whether or not a customer is a VIP customer when their MAX amount of points during one month exceeds 60. You have a list of records of the number of points a customer has accumulated per month over the last five years. While we touched on the use cases of MAX(CASE WHEN), let us explore the possible uses for its inverse (i.e., CASE WHEN MAX()). To find the previous order on that day, you can use the following query: Select MAX ( Case when OrderDate '' then OrderDate end ) as earliest_possible_order from OrdersĬase 4: CASE WHEN MAX(), conditionals on the max value Let us look at the following scenario as an example:Ĭompany ABC decided to trace their orders placed within the year 1996 and would like to find the last order that year as a specific anomaly happened with that particular order. When taking a date as a parameter, the MAX function returns the latest data available, while the MIN function returns the converse. We will be learning about MAX(CASE WHEN) with dates for this case. The MAX function is versatile and can return more than just numeric values it can also work with unique identifiers, characters, and dates. Given the following code: Select inflated_price, Case when inflated_price 80 then inflated_price end ) as idealprice from Products To consider edge cases, we use the ELSE keyword. It plays an integral role in handling edge cases and exceptions. The ELSE keyword in SQL handles the control whenever all the CASE statements fail, i.e., when all the CASE statements do not manage to return true. CASE WHEN THEN 'THIS IS EXECUTED' WHEN THEN 'THIS IS NOT EXECUTED' Unlike most programming languages, when a CASE statement is verified as accurate, it automatically stops and does not look for a break directive. When the condition inside the WHEN statement is met (i.e., true), the THEN statement is then executed. WHEN specifies a series of conditions that can be evaluated as either true or false. The CASE statement initializes the control structure, telling the SQL interpreter that a series of WHEN statements are to follow.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |