|
Custom Search
| |
|
Use LEAST function to find the SMALLEST value from multiple arguments In this tutorial, we will learn how to use MySQL LEAST function to find the smallest value from two or more arguments. The arguments can be pure values and/or values from table fields/columns. It's important to understand the differences between MySQL MIN function and LEAST function.
When using LEAST function, please consider the following rules:
Syntax:
or
or
As shown above, the arguments can mix actual values and column names of a table. Create the sample table for this tutorial To demonstrate the LEAST function, we need to create a table called quarterly_sales which contains quarterly sales amounts for each product in 1997 in Northwind database. We'll use this table to do our tutorial queries.
create table quarterly_sales as select a.ProductName, round(sum(case quarter(c.OrderDate) when '1' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr1, round(sum(case quarter(c.OrderDate) when '2' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr2, round(sum(case quarter(c.OrderDate) when '3' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr3, round(sum(case quarter(c.OrderDate) when '4' then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr4 from Products a inner join Order_Details b on a.ProductID = b.ProductID inner join Orders c on c.OrderID = b.OrderID inner join Customers d on d.CustomerID = c.CustomerID where c.OrderDate between date('1997-01-01') and date('1997-12-31') group by a.ProductName order by a.ProductName, d.CompanyName; The table created contains 77 products with Quarter 1 to Quarter 4 sales figures.
Practice 1: The query below returns the smallest value in the arguments based on what the context the list is in.
SELECT LEAST(2, 5, 0) AS integer_context, LEAST(2, 5, 0, null) AS integer_context, LEAST(34.0, 3.0, 5.0, 767.0) AS real_context, LEAST('B', 'a', 'c', 'd') AS case_insensitive_context, LEAST('B', 'a', 5, 7, 'c', 'd') AS mixed_context;
1 row returned: Practice 2: Case sensitive string comparison. The two queries below instruct MySQL to perform case-sensitive string comparison by using COLLATE operator with utf8mb4_0900_as_cs or utf8mb4_bin collation. Please note: utf8mb4_bin and utf8mb4_0900_as_cs have the same effect - bin is short for binary and _cs stands for case-sensitive.
SELECT LEAST('B', 'b', 'c', 'C') COLLATE utf8mb4_bin AS case_sensitive_context; SELECT LEAST('B', 'b', 'c', 'C') COLLATE utf8mb4_0900_as_cs AS case_sensitive_context;
1 row returned: Practice 3: Compare data across multiple columns This query compares sales figures across the four quarters for each product and return the minimum sales amount.
select ProductName, LEAST(qtr1, qtr2, qtr3, qtr4) as Min_quarterly_sales from quarterly_sales;
77 rows returned: Practice 4: Compare data across multiple columns and indicate which column has the smallest value This query does the same thing as the query above, but in addition, it also works out which quarter has the smallest sales. Please note that if two columns contain the same smallest values, the LEAST function returns the value in the first encountered column based on the order of columns in table structure. For example, product Chef Anton's Gumbo Mix has both 0 sales in Quarter 1 and Quarter 2, but Quarter 1 is returned in the result (see Min_Quarter column).
select ProductName, LEAST(qtr1, qtr2, qtr3, qtr4) as Min_quarterly_sales, case when LEAST(qtr1, qtr2, qtr3, qtr4)=qtr1 then 'Qtr 1' when LEAST(qtr1, qtr2, qtr3, qtr4)=qtr2 then 'Qtr 2' when LEAST(qtr1, qtr2, qtr3, qtr4)=qtr3 then 'Qtr 3' else 'Qtr 4' end as Min_Quarter from quarterly_sales;
77 rows returned:
Happy Coding!
Other tutorials in this category 1. Using String Functions, Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |