Sorting and filtering data is an essential aspect of working with databases. MySQL provides powerful capabilities to sort and filter data using various SQL clauses and operators.
Sorting data allows you to arrange query results in a specified order based on one or more columns. MySQL offers the ORDER BY
clause to achieve this.
For instance, we have a products
table with columns product_id
, product_name
, and price
. To create and insert data in the table, run the following query.
CREATE TABLE products (product_id INT,product_name VARCHAR(255),price DECIMAL(10, 2));INSERT INTO products (product_id, product_name, price) VALUES(1, 'Laptop', 800),(2, 'Smartphone', 600),(3, 'Headphones', 100),(4, 'Keyboard', 50),(5, 'Mouse', 20),(6, 'Monitor', 300),(7, 'Printer', 150),(8, 'External Hard Drive', 120),(9, 'USB Flash Drive', 15),(10, 'Speaker', 80);
Now, to sort the products by price in ascending order (ASC
), we can execute the following query:
SELECT * FROM products ORDER BY price ASC;
Filtering data allows you to fetch specific records based on defined criteria. MySQL offers the WHERE
clause to achieve this.
Let’s say we want to retrieve all products with a price
greater than $50. We can use the following query:
SELECT * FROM products WHERE price > 50;
In many cases, we may need to sort and filter data at the same time. MySQL allows you to combine the ORDER BY
and WHERE
clauses to achieve this.
To retrieve products with a price
greater than $50, with price
in descending order (DESC
) , we can use the following query:
SELECT * FROM products WHERE price > 50 ORDER BY price DESC;
MySQL offers various comparison operators and logical operators to create complex filtering conditions. Here are some commonly used operators:
Comparison operators: =
, <>
(not equal), >
, <
, >=
, <=
Logical operators: AND
, OR
, NOT
If we want to retrieve products with a price
between $50 and $200, excluding any products with product_name
equal to 'Speaker'
, we can use the following query.
SELECT * FROM products WHERE price >= 50 AND price <= 200 AND product_name <> 'Speaker';
Sorting and filtering data in MySQL is important for retrieving and manipulating specific records from a database. By utilizing the ORDER BY
and WHERE
clauses, along with comparison and logical operators, you can effectively sort and filter data in MySQL. Whether you need to arrange query results in a specific order or retrieve records based on specific conditions, MySQL provides the necessary tools to accomplish these tasks.
Free Resources