SQL-Sorting and Filtering Data

5 min readJan 13, 2023

Types of ordering / sorting

This part delves into the use of the ORDER BY clause for organizing data. We will cover the function of the ORDER BY clause and its various forms for sorting data in a table. The goal of this reading is to provide practical examples of how to use the ORDER BY clause in real-world scenarios.

ORDER BY is a clause in SQL (Structured Query Language) used to sort the data in a table based on one or more columns. It is used in the SELECT, UPDATE and DELETE statements. The clause orders the rows in ascending or descending order based on the values in the specified columns. The default sort order is ascending. Syntax for using

ORDER BY is:
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) [ASC or DESC];

ASC is used to sort the data in ascending order and DESC is used to sort the data in descending order. If the order is not specified, it defaults to ascending order.

Here are a few examples of using the ORDER BY clause in different SQL statements:

SELECT statement:

SELECT first_name, last_name, age 
FROM employees 
ORDER BY last_name, first_name;

This query selects the first name, last name, and age columns from the “employees” table and sorts the results by last name and first name in ascending order.

UPDATE statement:

UPDATE products 
SET price = price + 10 
ORDER BY price DESC;

This query increases the price of all products by 10 and sorts the updated products by price in descending order.

DELETE statement:

DELETE FROM orders 
WHERE order_date < '2022-01-01' 
ORDER BY order_date ASC;

This query deletes all orders with a date earlier than January 1, 2022 and sorts the deleted orders by order date in ascending order.

Here are a few examples of more advanced uses of the ORDER BY clause:

Sorting by calculated values:

SELECT first_name, last_name, salary*12 AS annual_salary 
FROM employees 
ORDER BY annual_salary DESC;

This query calculates the annual salary of each employee by multiplying their salary by 12, and then sorts the results by the calculated annual salary in descending order.

Using the NULLS FIRST or NULLS LAST option:

SELECT product_name, expiration_date
FROM products
ORDER BY expiration_date NULLS LAST;

This query selects the product name and expiration date of all products and sorts the results by expiration date, with products with a NULL expiration date appearing last. If you want the NULL to appear first, use NULLS FIRST instead of NULLS LAST

Using the LIMIT clause with ORDER BY:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

This query selects the first name, last name, and salary of all employees and sorts the results by salary in descending order. The query then returns only the top 5 results using the LIMIT clause.

Using a subquery with ORDER BY:

SELECT first_name, last_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees)
ORDER BY last_name;

This query selects the first name and last name of all employees whose salary is equal to the maximum salary in the “employees” table and then sorts the results by last name. The subquery is used to find the maximum salary in the table.

In conclusion, the ORDER BY clause is a powerful tool in SQL that can be used to sort the data in a table based on one or more columns. The examples provided demonstrate the various ways in which the ORDER BY clause can be used in different SQL statements such as SELECT, UPDATE, and DELETE.

WHERE Clause Uses

The WHERE clause is used in SQL to filter the rows in a table based on certain conditions. It is used in SELECT, UPDATE and DELETE statements. It is used to retrieve only the rows from the table that meet a certain criteria.

Here are a few examples of using the WHERE clause with comparison and logical operators:

Using comparison operators:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

This query selects the first name, last name, and salary of all employees whose salary is greater than 50000.

Using logical operators:

SELECT first_name, last_name, salary, department
FROM employees
WHERE (salary > 50000 AND department = 'IT') OR (salary > 60000 AND department = 'HR');

This query selects the first name, last name, salary and department of all employees whose salary is greater than 50000 and belong to IT department or whose salary is greater than 60000 and belong to HR department.

Using NOT operator:

SELECT first_name, last_name, salary, department
FROM employees
WHERE NOT (department = 'IT');

This query selects the first name, last name, salary and department of all employees who do not belong to the IT department.

Using BETWEEN operator:

SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

This query selects the first name, last name, and salary of all employees whose salary is between 40000 and 60000.

Using IN operator:

SELECT first_name, last_name, salary
FROM employees
WHERE department IN ('IT', 'HR');

This query selects the first name, last name, and salary of all employees whose department is either IT or HR.

Using LIKE operator:

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%gmail.com';

This query selects the first name, last name, and email of all employees whose email address ends with “gmail.com”. The ‘%’ is a wildcard character that matches any string.

Using IS NULL operator:

SELECT first_name, last_name, address
FROM employees
WHERE address IS NULL;

This query selects the first name, last name, and address of all employees who have a null value in the address field.

Using IS NOT NULL operator:

SELECT first_name, last_name, phone_number
FROM employees
WHERE phone_number IS NOT NULL;

This query selects the first name, last name, and phone number of all employees who have a non-null value in the phone number field.

Using the EXISTS operator:

SELECT first_name, last_name
FROM employees
WHERE EXISTS (SELECT * FROM orders WHERE orders.employee_id = employees.employee_id);

This query selects the first name and last name of all employees who have at least one order in the “orders” table. The subquery retrieves all rows from the “orders” table where the employee ID matches the employee ID of the current row in the “employees” table.

Using the ALL operator:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'IT');

This query selects the first name, last name, and salary of all employees whose salary is greater than the highest salary of all employees in the IT department.

Using the ANY operator:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'HR');

This query selects the first name, last name, and salary of all employees whose salary is greater than the salary of at least one employee in the HR department.

Using the NOT IN operator:

SELECT first_name, last_name, salary
FROM employees
WHERE department NOT IN ('IT', 'HR');

This query selects the first name, last name, and salary of all employees whose department is neither IT nor HR.

Using the REGEXP operator:

SELECT first_name, last_name, email
FROM employees
WHERE email REGEXP '^[A-Z].*@gmail.com$';

This query selects the first name, last name, and email of all employees whose email address starts with an uppercase letter, ends with “@gmail.com” and any characters in between.

These examples demonstrate the various ways in which the WHERE clause can be used in SQL to filter data based on various conditions using different comparison and logical operators and functions.

SQL-Sorting and Filtering Data

Types of ordering / sorting

WHERE Clause Uses

Written by Ahmet Taşdemir