📚Back-end Notes

8.Sorting and filtering data

Oct 04, 202418 min read

ORDER BY clause

There are several clauses available in SQL for sorting and filtering data in a table. One of the most useful of these is the ORDER BY clause. With this clause, you can reorder the data in a table by one or more columns. For example, in a table that holds data on students in a college, you could sort the data by date of birth. The table would then present all students in the order of oldest to youngest.

Purpose of the ORDER clause

The ORDER BY clause is an optional clause that can be added to a select statement, its purpose is to help sort data in either ascending or descending order.

SELECT column_1, column_2, column_3...
FROM table_name
ORDER BY column_name ASC |
                     DESC;

Sorting multiple columns

SELECT column_1, column_2, column_3...
FROM table_name
ORDER BY column1_name ASC, column2_name DESC; 
SELECT *
FROM table_name
ORDER BY column1_name, column2_name ASC|DESC; 

SELECT ID, first_name, last_name, nationality
FROM student_tbl
ORDER BY nationality ASC;

NOTE

Note that even if I was to omit ASC from the end of my code, I’d still get the same result. This is because the ORDER BY clause sorts all data in ascending order by default.

SELECT ID, first_name, last_name, date_of_birth, nationality
FROM student_tbl
ORDER BY nationality ASC, date_of_birth DESC;

Types of ordering / sorting

In this reading, you’ll explore using the ORDER BY clause for sorting data. You’ve learned about the purpose of the ORDER BY clause and the different forms in which it can be used to sort data. The main objective of this reading is to present some more examples related to some practical scenarios of using the ORDER BY clause for sorting data in a table.  

The ORDER BY clause

The ORDER BY clause is useful when you want to sort or order the results obtained when running a SQL SELECT query. Data in a database become more meaningful when they are ordered or sorted in a specific order. Ordered data helps people make more accurate business decisions effectively and efficiently. 

In SQL, there’s the ORDER BY clause that can help you achieve this. If you run a SQL SELECT query, you get a set of unsorted results. If you want to sort them, you need to add the special ORDER BY clause into the SQL SELECT statement. 

It can be used after the FROM clause as in:

SELECT *  
FROM Employee 
ORDER BY <order by column name>;

After the ORDER BY keyword, you need to specify the column name based on the data that needs to be sorted. Optionally, you can specify the keywords ASC or DESC after the column name. This is to indicate if the ordering should be in ascending or descending order.

Ascending and descending order are the two main types of ordering. If ASC or DESC are not specified, the data is sorted by default in ascending order. The ASC and DESC keywords sort the data based on the order by column, taking into consideration the data type of column or field, namely integer, numeric, text and dates.

Working with the ORDER BY clause

Let’s review some example scenarios that use the ORDER BY clause using the tables in the sample database. You can give SQL SELECT statements using the ORDER BY clause with ASC and DESC keywords as required for these scenarios.

Sorting by a single column

In the customer table, the data is sorted by default in ascending order within the customer ID field. The customer ID field is numeric, so the data is sorted in ascending numeric order. Now let’s examine how to order this data in the descending order of the Customer ID field. 

To do this, you can run the following query:

SELECT * 
FROM customers 
ORDER BY CustomerId DESC;

In the output, the records are sorted in descending order (largest to smallest) of the CustomerIdwhich has a numeric data type.

Now let’s examine how sorting happens for a text data typed column. Consider sorting the data by the City column which has a text data type of VARCHAR. If you want to sort the customer data by city, use the following SELECT statement:

SELECT * 
FROM customers 
ORDER BY City;

An ordering method like ASC or DESC wasn’t specified in the ORDER BY clause. So, by default, the ordering happens in ascending order.

If you review the City column, you’ll notice that the data is sorted in ascending alphabetical order (A to Z). 

Let’s now run the following SELECT statement:

SELECT * 
FROM customers 
ORDER BY City DESC;

Now you’ll notice that the records are ordered in descending alphabetical order (Z to A).

Let’s examine another example of how data is ordered in a sort column that uses a DATE type field. This example uses the invoices table in the sample database. You can use the following SQL SELECT statement to sort the data by the invoice date column:

SELECT * 
FROM invoices 
ORDER BY InvoiceDate;

If you review the InvoiceDate column, you’ll notice that the date values are sorted from smallest to largest. That is, they’re sorted in ascending order, which is the default sort order.Now let’s try running this query with the DESC keyword added in the ORDER BY clause.

SELECT * 
FROM invoices 
ORDER BY InvoiceDate DESC;

The data is now sorted from the largest to smallest date, which is descending order.

Ordering by multiple columns

You can also sort data by multiple columns and apply different sort orders to them. Let’s say you want to sort invoice data by both billing city and invoice date. To do this, run the following query:

SELECT * 
FROM invoices 
ORDER BY BillingCity ASC, InvoiceDate DESC;

You’ll notice that the data is sorted in ascending order of BillingCity. That’s why the data in the BillingCity column is sorted in alphabetical order. The data of the InvoiceDatecolumn is in turn sorted in descending order.

For example, if you review the records with the billing city of Amsterdam, the invoice dates are ordered in descending order from largest to smallest date. Similarly, if you examine the other sets of data closely, you’ll observe the same.

The main types of ordering in SQL are ASC, ascending, and DESC, descending. How the data is ordered in these two cases would depend on the data type of the field or column being used as the sort column.

WHERE clause

WHERE

The WHERE clause is used to filter data, more specifically, it is used to filter and extract records that satisfy specified a condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The condition makes it possible to filter out and fetch the required records from the table. You can think of the conditions as filter criteria, only the records that meet the condition will be retrieved.

SELECT column1, column2, ...
FROM table_name
WHERE column = value;

The operand can be either a text value or a numeric value. It all depends on the data type of the table, column or field.

SELECT column1, column2, ...
FROM table_name
WHERE student_id = 01;
SELECT column1, column2, ...
FROM table_name
WHERE first_name = 'John';

Operator

  • Equal to
  • Less than
  • Greater than
  • Less than or equal to
  • Greater than or equal to
  • Not equal to

In addition to these symbols, the WHERE clause can also use the BETWEEN, LIKE and AND operators.

  • With the BETWEEN operator, you can filter records within a specific numeric or time and date range.
  • The LIKE operator is used to specify a pattern in the WHERE clause filter criteria.
  • And the IN operator is used to specify multiple possible values for a column.

SELECT *
FROM student_tbl
WHERE faculty = 'Engineering';

SELECT *
FROM student_tbl
WHERE data_of_birth BETWEEN '2010-01-01' AND '2010-06-30';

SELECT *
FROM student_tbl
WHERE faculty LIKE 'Sc%';

NOTE

The percentage character in the pattern is a wild card character that represents 01 or multiple characters. The underscore sign can also be used to represent one single character.

In this case my WHERE clause asks my SQL to search for and filter right values within the faculty column that start with Sc followed by any number of characters.

SELECT *
FROM student_tbl
WHERE country IN('USA','UK');

NOTE

Note that although the examples in this video looked at the WHERE clause in the SELECT statement, it can also be used in other statements, such as UPDATE and DELETE.

WHERE Clause uses

In this reading, you’ll explore the usage of the WHERE clause for filtering data. You’ve learned about the purpose and the syntax of the WHERE clause. You’ve also learned how it behaves with different types of operands (namely text-based or numeric) based on the data type of the table column. You explored the types of operators that can be used in the WHERE clause. The main objective of this reading is to present some more examples and scenarios in which the WHERE clause is used to filter data in a table.

The WHERE clause

The WHERE clause is useful when you want to filter data in a table based on a given condition in the SQL statement.The WHERE clause in SQL is there for the purpose of filtering records and fetching only the necessary records. This can be used in SQL SELECT, UPDATE and DELETE statements.

The filtering happens based on a condition. The condition can be written using any of the following comparison or logical operators.

Comparison operators 

OperatorDescription
=Checks if the values of two operands are equal or not. If yes, then condition becomes true.
!=Checks if the values of two operands are equal or not. If values are not equal, then condition becomes true.
<>Checks if the values of two operands are equal or not. If values are not equal, then condition becomes true.
>Checks if the value of the left operand is greater than the value of the right operand. If yes, then condition becomes true.
<Checks if the value of left operand is less than the value of right operand. If yes, then condition becomes true.
>=Checks if the value of the left operand is greater than or equal to the value of right operand. If yes, then condition becomes true.
⇐Check if the value of the left operand is less than or equal to the value of the right operand. If yes then condition becomes true.
!<Checks if the value of the left operand is not less than the value of the right operand. If yes, then condition becomes true.
!>Checks if the value of the left operand is not greater than the value of the right operand. If yes, then condition becomes true.

Logical operators 

OperatorDescription
ALLUsed to compare a single value to all the values in another value set.
ANDAllows for the existence of multiple conditions in an SQL statement’s WHERE clause.
ANYUsed to compare a value to any applicable value in the list as per the condition.
BETWEENUsed to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTSUsed to search for the presence of a row in a specified table that meets a certain criterion.
INUsed to compare a value to a list of literal values that have been specified.
LIKEUsed to compare a value to similar values using wildcard operators.
NOTReverses the meaning of the logical operator with which it is used. For example: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
ORUsed to combine multiple conditions in an SQL statement’s WHERE clause.
IS NULLUsed to compare a value with a NULL value.
UNIQUESearches every row of a specified table for uniqueness (no duplicates).

Using the sample database, let’s review an example that uses the comparison operator > (greater than) to formulate the WHERE clause condition to filter criteria. If you want to fetch the invoices that have a total value of more than $2, you will need to filter out the records in the invoicetable by using the WHERE clause in the SELECT statement. 

To perform this action, you can run the following query:

SELECT *   
 
FROM invoices  
 
WHERE Total > 2;

You’ll notice that this query filters out the records based on the condition given in the WHERE clause Total > 2. It brings in only the records that have a totalfield value of more than $2. But what if you want to combine multiple conditions in the WHERE clause? Multiple conditions in the WHERE clause can be combined using the AND / OR logical operators. Therefore, these two operators are also known as conjunctive operators.

The syntax required to use the AND operator in the WHERE clause of a SELECT statement is as follows:

SELECT column1, column2, columnN  
 
FROM table_name 
 
WHERE [condition1] AND [condition2]...AND [conditionN];

N can be any number. Here, for the entire condition to be TRUE, all conditions separated by the AND must be TRUE.

Let’s review an example. You need a list of invoices for which the total is over $2 and the BillingCountry is the USA. Here’s an example of how the WHERE clause condition can be given in the SELECT statement:

SELECT *  
 
FROM invoices 
 
WHERE Total > 2 AND BillingCountry = 'USA';

Here, the AND operator is used as a conjunctive operator to combine the two conditions Total > 2 AND BillingCountrywhich is the USA. You’ll receive the invoice records with a total bill value of more than $2 with the USA as billing country. This means that for a record to be included in the result, both the conditions should be true. Similarly, the OR operator can also be used to combine multiple conditions in the WHERE clause. 

The syntax is as follows:

SELECT column1, column2, columnN  
 
FROM table_name 
 
WHERE [condition1] OR [condition2]...OR [conditionN]

Let’s continue to use the same invoicestable for the next example. If you want to get a list of invoices for which the BillingCountryis the USA or France, how would you use the OR operator to combine the two conditions?

You can write the following SQL syntax:

SELECT *  
 
FROM invoices 
 
WHERE BillingCountry = 'USA' OR BillingCountry='France';

You’ll notice that the result consists of records where the billing country is the USA or France. This means that for a record to be included in the result, either condition should be true. 

Let’s consider another scenario. If you want to get a list of invoices where the total value is over $2 and the BillingCountry is USA or France, here’s the syntax for the SELECT query using both AND / OR conjunctive operators together in the WHERE clause:

SELECT *  
 
FROM invoices  
 
WHERE Total > 2 AND (BillingCountry = 'USA' OR BillingCountry = 'France');

You’ll notice that it has filtered out the invoice records that have a total value of more than $2.From that result, it has also filtered out the records that have a country value of either the USA or France. In the query, the two conditions combined with the OR operator are surrounded by a pair of parentheses to ensure that they are evaluated as one single expression. 

The other SQL logical and comparison operators which were not demonstrated in this reading can also be used in the WHERE clause. In addition, the WHERE clause can also be used with UPDATE and DELETE statements. To learn more, consult the additional resources reading of this lesson.

SELECT DISTINCT clause

how can you retrieve the results you’re looking for without any duplicates → select distinct

Let’s assume you want to find out which countries the students in the college are from. So that you can get an understanding of which nationalities are represented in the college.

SELECT country
FROM student_tbl;

Running this select query gives you seven records as the result with multiple duplicate records. In this case there are duplicate records for Australia and the USA.

You can use the select distinct statement. You can write a select statement just like before but this time distinct after the word select, the word distinct will return all unique values in the table with no duplicates.

SELECT DISTINCT country 
FROM student_tbl;
SELECT DISTINCT faculty, country FROM student_tbl;

So with this statement which uses multiple columns, I’ve generated each unique faculty and country combination.

SELECT DISTINCT faculty, country
FROM student_tbl;

SELECT DISTINCT clause in use

In this reading, you’ll explore the usage of SELECT DISTINCT to retrieve a unique set of values in a SELECT statement. You’ve learned about the purpose and the syntax of SELECT DISTINCT and how it behaves in a SELECT statement. The main objective of this reading is to present some more examples and practical scenarios that use the DISTINCT keyword in the SELECT statement. 

The DISTINCT keyword

DISTINCT is useful for retrieving a set of unique values when there are duplicate column values in a table. It is used with the SELECT statement, so it’s commonly referred to as SELECT DISTINCT. In short, what DISTINCT does is to findunique values within a column, or columns, of a table.

Let’s look at some examples of how the DISTINCT keyword behaves using a few data retrieval scenarios from the table in the sample database.

Using SELECT DISTINCT on a single column

If there’s a table named invoices with the same BillingCountryrepeated in many instances, you can run the following query to identify what they are:

SELECT BillingCountry  
 
FROM invoices 
 
ORDER BY BillingCountry;

When you look at the result, you’ll notice that there are duplicate values in the BillingCountry column. How can you obtain a list of unique billing countries where the invoices have been raised? Let’s change the SELECT statement by adding the DISTINCT keyword and then run it again.

SELECT DISTINCT BillingCountry  
 
FROM invoices 
 
ORDER BY BillingCountry;

This time, the duplicate values are gone and only a unique set of billing countries are returned as the result. Where there are repeating values in the BillingCountry column, for example for Argentina, Australia and Austria. The above SELECT DISTINCT query will eliminate those duplicate rows and generate the result as a unique set of values.

Using SELECT DISTINCT on multiple columns

If you inspect the values in the BillingCountryand BIllingCitycolumns, you’ll notice that the same billing City repeats for a single billing country. You can run the following code to verify this.

SELECT BillingCountry, BillingCity  
 
FROM invoices;

So how can you generate list of unique billing cities within the billing countries?

You can run a query that adds the DISTINCT keyword to the SELECT statement.

SELECT DISTINCT BillingCountry, BillingCity   
 
FROM invoices 
 
ORDER BY BillingCountry, BillingCity;

Note: The ORDER BY clause is added here to sort the values for easy reference.

The result is a unique set of billing cities retrieved for the billing countries. Basically, there are no duplicate values in the  BillingCity column. In other words, when you do a DISTINCT of multiple columns, it looks for a combination of unique values in all those columns. In this example, all combinations of BillingCountryand  BillingCity in the result are unique.

NULL values in a DISTINCT column

Let’s say there are NULL values in a DISTINCT column(s). For example, in the BillingCity column. You can run the same query as before to get the unique billing cities within the billing countries.

SELECT DISTINCT BillingCountry, BillingCity   
 
FROM invoices 
 
ORDER BY BillingCountry, BillingCity;

Provided that for some records the BillingCity column has NULL values, you’ll receive records with a combination of some value for BillingCountry and NULL for BillingCity.

So, it’s important to know that SELECT DISTINCT treats any NULL values in the DISTINCT column(s) as unique. Therefore, in this case, it looks for a combination of unique BillingCountry and BillingCity values. Any NULL values in the BillingCity column are considered unique values. For example, Argentina – NULL could be one unique combination and Australia – NULL could be another.

Using DISTINCT with SQL aggregate functions

DISTINCT can also be used with SQL aggregate functions like COUNT, AVG, MAX and so on. In this case, you must specify an expression that’s written using some aggregate function. Therefore, it’s not only column names that you can use DISTINCT with but also with expressions.

What if you want to find out the number of unique countries of the customers in the customer table? Run a SELECT statement that uses the aggregate function COUNT on the country column along with DISTINCT.

For example:

SELECT COUNT(DISTINCT country)  
 
FROM customers;

The result that you get is the number of unique countries that the customers come from. Using DISTINCT on the country column/field gives a unique list of countries and the COUNT aggregate function counts the number of results.

Here are some important points to remember in terms of SELECT DISTINCT:

  • When only one column or expression is provided in the DISTINCT clause, the query will return the unique values for that column. 
  • When more than one column or expression is provided in the DISTINCT clause, the query will retrieve unique combinations for those columns. 
  • The DISTINCT clause doesn’t ignore NULL values in DISTINCT column(s). NULL values are considered as unique values by DISTINCT.

Additional resources

Here is a list of resources that may be helpful as you continue to explore database engineering.

DatabaseSQLDataSyntax

Previous one → 7.SQL operators | Next one → 9.Designing database schema

Graph View

Backlinks