Filtering and Sorting in SQL
When working with SQL, filtering and sorting data is essential to extract meaningful insights. Below are some key operations: DISTINCT, ORDER BY, LIMIT/OFFSET, and advanced filtering with BETWEEN, IN, and LIKE.
1. DISTINCT: Removing Duplicates
The DISTINCT keyword is used to return only unique (distinct) values, eliminating duplicates from the result set.
Syntax:
SELECT DISTINCT column1, column2, ...
FROM table_name;
Example:
Let’s say you have a table called products:
id name category
1 Laptop Electronics
2 Phone Electronics
3 T-shirt Clothing
4 Jacket Clothing
5 Phone Electronics
To retrieve distinct product categories:
SELECT DISTINCT category FROM products;
Result:
category
-----------
Electronics
Clothing
In this case, even though "Electronics" appears multiple times, it is returned only once due to the DISTINCT keyword.
2. ORDER BY: Sorting Data
The ORDER BY clause is used to sort the result set in either ascending (ASC) or descending (DESC) order based on one or more columns.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
Example:
To retrieve all products ordered by their names in alphabetical order:
SELECT * FROM products
ORDER BY name ASC;
Result:
id | name | category
-------------------------
4 | Jacket | Clothing
1 | Laptop | Electronics
2 | Phone | Electronics
5 | Phone | Electronics
3 | T-shirt | Clothing
To sort by category first and then by name within each category:
SELECT * FROM products
ORDER BY category ASC, name ASC;
Result:
id | name | category
-------------------------
4 | Jacket | Clothing
3 | T-shirt | Clothing
1 | Laptop | Electronics
2 | Phone | Electronics
5 | Phone | Electronics
3. LIMIT and OFFSET: Limiting Results
The LIMIT clause is used to specify the maximum number of records to return, while the OFFSET clause is used to skip a specific number of rows before starting to return rows.
Syntax:
SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET start_row;
Example:
Let’s say you want to retrieve only the first 3 products:
To limit the result to 3 rows:
SELECT * FROM products
LIMIT 3;
Result:
id | name | category
-------------------------
1 | Laptop | Electronics
2 | Phone | Electronics
3 | T-shirt | Clothing
To skip the first 2 rows and retrieve the next 3 rows:
SELECT * FROM products
LIMIT 3 OFFSET 2;
Result:
id | name | category
-------------------------
3 | T-shirt | Clothing
4 | Jacket | Clothing
5 | Phone | Electronics
4. BETWEEN: Range Filtering
The BETWEEN operator is used to filter the result set within a certain range of values, including the boundary values.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example:
Let’s say you have a sales table with the following data:
id product amount
1 Laptop 1000
2 Phone 600
3 Jacket 150
4 T-shirt 50
5 Headphones 80
To retrieve sales where the amount is between 100 and 600:
SELECT * FROM sales
WHERE amount BETWEEN 100 AND 600;
Result:
id | product | amount
-----------------------
2 | Phone | 600
3 | Jacket | 150
5 | Headphones| 80
5. IN: Matching Multiple Values
The IN operator allows you to specify multiple values in a WHERE clause, filtering records where a column matches any of the listed values.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);
Example:
To retrieve sales for products 'Laptop' and 'Phone':
SELECT * FROM sales
WHERE product IN ('Laptop', 'Phone');
Result:
id | product | amount
----------------------
1 | Laptop | 1000
2 | Phone | 600
6. LIKE: Pattern Matching
The LIKE operator is used for pattern matching in string values. It allows you to filter records based on a pattern, often using wildcards (% and _).
% represents zero, one, or many characters.
_ represents a single character.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
Example:
To retrieve all products where the name starts with 'T':
SELECT * FROM products
WHERE name LIKE 'T%';
Result:
id | name | category
-------------------------
3 | T-shirt | Clothing
To retrieve all products where the name ends with 'e':
SELECT * FROM products
WHERE name LIKE '%e';
Result:
id | name | category
-------------------------
2 | Phone | Electronics
5 | Phone | Electronics
To retrieve products with exactly 6-letter names:
SELECT * FROM products
WHERE name LIKE '______';
Result:
id | name | category
-------------------------
1 | Laptop | Electronics