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