Nisarg Upadhyay
Sort and Filter: Order by on expression

Learn MySQL: Sorting and Filtering data in a table

July 28, 2020 by

In this article, we will learn how we can sort and filter data using the WHERE clause and sort the data using the ORDER BY clause. In my previous article, Learn MySQL: Querying data from MySQL server using the SELECT statement, we learned how to generate the ER diagram using reverse-engineering the database using MySQL workbench and basics of SELECT statement and usage. Ins this article, we will learn how we can sort and filter data using the WHERE clause and sort the data using the ORDER BY clause.

Filtering data using the WHERE clause

To filter data, we use the WHERE clause in the SELECT query. The syntax is as follows:

Here, the condition can be combined with one or more than one logical expression. These logical expressions are called predicates. The predicates or logical expression can be evaluated as TRUE, FALSE, or UNKNOWN. When the WHERE clause does not return any row, then the predicated is evaluated as FALSE. If it returns the row, it will be included in the result-set, and the predicate is evaluated as TRUE.

To demonstrate, we are going to use the customer table of the sakila database. In this article, I will demonstrate various use cases of the WHERE clause and plan to cover the following use cases:

  1. The WHERE clause with an equal operator
  2. The WHERE clause with AND, OR, and BETWEEN operators
  3. The WHERE clause with LIKE and IN operators
  4. The WHERE clause with comparison operators

The WHERE clause with an equal operator

The following query populates the list of customers whose first name is ‘LINDA‘, to retrieve the records, the query should be written as follows:

The output is below:

Sort and Filter: Single predicate

In the query, the predicate is WHERE first_name = ‘LINDA ‘, which is evaluated as true.

The WHERE clause with AND operator

In this example, I will show how we can use multiple predicates to populate the desired records from the table. For example, we want to populate the customer whose address_id is 598 and store_id=1. To retrieve the records from the table, the query should be written as follows:

The following is the output:

Sort and Filter: multiple predicate with AND operator

Here we are including AND operator in the WHERE clause. The expression is WHERE address_id=598 and store_id=1. If both expressions are evaluated as true, then the entire expression is evaluated as TRUE. In the table, we have a record whose address_id=598 and store_id=1; therefore, the entire expression is evaluated as TRUE, and query returned the result-set.

The WHERE clause with OR operator

For example, we want to populate the customer whose store_id=1 OR active=0. To retrieve the records, the query should be written as follows:

The following is the output:

Sort and Filter: multiple predicate with OR operator

Here we are including OR operator in the WHERE clause. The expression is WHERE store_id=1 OR active=0. From both expressions, any one of them evaluated as true; then, the entire expression is evaluated as TRUE. In the table, we have a record whose store_id=1 OR active=0; therefore, the entire expression is evaluated as TRUE, and query returned the result-set.

The WHERE clause with BETWEEN operator

For example, we want to populate the customer whose address_id is between 560 and 570. To retrieve the records, the query should be written as follows:

The following is the output:

Sort and Filter: single predicate with BETWEEN operator

Here, we included the BETWEEN operator in the WHERE clause. The expression is WHERE address_id is BETWEEN 560 and 570. In the table, we have records where address_id is BETWEEN 560 and 570; therefore, the expression is evaluated as TRUE, and query returned the result-set.

The WHERE clause with IN operator

For example, we want to populate the customer whose customer_id in (10,11,12). To retrieve the records, the query should be written as follows:

The following is the output:

Sort and Filter: single predicate with IN  operator

Here, we included the IN operator in the WHERE clause. The expression is WHERE customer_id in (10,11,12). In the table, we have a record whose customer_id in (10,11,12); therefore, the expression is evaluated as TRUE, and query returned the result-set.

The WHERE clause with a comparison operator

In the WHERE clause, we can specify the following comparison operators to match the values in the table. The details of the operators are as following:

Comparison Operator

Note

Equal to (=)

This operator can be used with any data type

Not Equal to (<> OR !=)

This operator can be used with any data type

Is greater or equal to (>=)

This operator can be used with numeric and date-time data types

Is greater or equal to (<=)

This operator can be used with numeric and date-time data types

Is greater than (>)

This operator can be used with numeric and date-time data types

Is less than (<)

This operator can be used with numeric and date-time data types

For example, we want to populate the records whose address id is greater than 100; then the query should be written as follows:

Below is the output:

Sort and Filter: single  predicate with > operator

Suppose, we want to get the list of the inactive customers from the table. The query should be written as follows:

Below is the output.

Sort and Filter: multiple predicate with equal to operator

Now, we want to populate the list of the customers who have been created after 12:47 PM on 22-07-2018. The query should be written as follows:

Below is the output:

Sort and Filter: single predicate with > operator

Sorting data using ORDER BY clause

When a SQL query returns the output, the values are not sorted. To sort the result of a query, we use the ORDER BY clause. The syntax of the ORDER BY clause is the following:

When you specify ASC in the ORDER BY clause, the result will be sorted in ascending order, and if you specify DESC, then the result will be sorted in descending order.

The default sorting order is Ascending, so if we do not specify the sorting order, then the query result will be sorted in ascending order.

We can specify one or more than one column in the ORDER BY clause. The columns and their sorting order must be separated by comma (,). We can specify different sorting orders for the individual column. For example, if you want to sort the first column in ascending order and second column in descending order, then the syntax will be Column_1 ASC, column_2 desc. Here first, the column_1 will be sorted in ascending order and then the column_2 will be sorted in descending order. When the second column is sorted, the order of the values of the first column does not change.

For example, I want to see the list of customers in ascending order. The query should be written as follows:

The following is the screenshot of the output:

Sort and Filter: Order by on one column

Another example, suppose I want to see the first name of the customer in ascending order and the last name in descending order then, the query should be written as follows:

The output is below:

Sort and Filter: Order by on multiple columns

We can use the ORDER BY clause to sort the result set that is generated by the expression. To demonstrate, I am going to use the rental table of the sakila database. Suppose the rental of the film has been increased. We want to check the updated price of the film, and the sorting must be performed on the updated rental. The query should be written as follows:

Below is the screenshot of the output:

Sort and Filter: Order by on expression

Similarly, we can sort the output of the result-set generated by the arithmetic function. For example, I want to find the highest number of films available in the store. The query should be written as follows:

Below is the screenshot of the output:

Sort and Filter: Order by on arithmetic function.

Summary

In this article, we have learned to Sort and filter data generated by a query using the WHERE and ORDER BY clause. I have covered various use cases of the WHERE and ORDER BY clause. In the next article, we will learn about the INSERT statement to add data in the MySQL table and its various use cases with examples. Stay tuned..!!

Table of contents

Learn MySQL: Querying data from MySQL server using the SELECT statement
Learn MySQL: What is pagination
Learn MySQL: Sorting and Filtering data in a table
Learn MySQL: Add data in tables using the INSERT statement
Learn MySQL: Create and drop temp tables
Learn MySQL: Delete and Update Statements
Learn MySQL: The Basics of MySQL Stored Procedures
Learn MySQL: The Basics of MySQL Views
Learn MySQL: An overview of MySQL Binary Logs
Learn MySQL: An overview of the mysqlbinlog utility
Learn MySQL: Run multiple instances of MySQL Server on Windows 10
Learn MySQL: MySQL String Functions
Learn MySQL: Control Flow functions
Learn MySQL: Install MySQL server 8.0.19 using a noinstall Zip archive
Learn MySQL: MySQL Copy table
Nisarg Upadhyay
MySQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

168 Views