Filter, Order, and Limit Your Query Results

Learning Objectives

After completing this unit, you’ll be able to:

• Filter query results using the WHERE clause with logical operators.
  
• Search for patterns in string data using the LIKE operator.
  
• Sort query results in ascending or descending order using ORDER BY.
  
• Limit the number of returned rows with the LIMIT and OFFSET clauses.
  

Filter Query Results with WHERE

In the previous unit, you learned how to write a simple query using SELECT and FROM. It's a good idea to focus your analysis on a specific subset of data to limit the results and hone in on what is useful. To do this, use the WHERE clause to filter the results.

The WHERE clause always comes after the FROM clause and allows you to specify conditions that must be met for a record to be included in your final result set.

Use Comparison and Logical Operators

You can use standard comparison operators (like =, >, <, !=) and logical operators (AND, OR) to define your criteria.

Let’s say you need to conduct a data quality check to find records that are missing a birth date, and you're only interested in records created after a specific date in 2024. To do this, you would use the following query.

SELECT "ssot__FirstName__c",
       "ssot__LastName__c",
       "ssot__CreatedDate__c"
FROM "ssot__Individual__dlm"
WHERE "ssot__createdDate__c" > DATE '2024-01-01'
AND "ssot__BirthDate__c" IS NULL

In this example:

• > is a comparison operator.
  
• AND is a logical operator that requires both conditions to be true.
  
• IS NULL is used to specifically check for records where the field is empty or undefined. You would use IS NOT NULL to exclude records where a value is missing.
  

You can also use the OR operator to return a record if either of the specified conditions is met. You can use parentheses () to group conditions for complex logic and clarity.

Search for Patterns with LIKE

Sometimes you don't know the exact value you're looking for, or you want to match a group of related text values (strings). The LIKE operator is used for pattern matching against text fields.

The LIKE operator is used with wildcards.

• Percent sign (%): Matches any sequence of zero or more characters.
  
• Underscore (_): Matches any single character.
  

For example, if you want to see all last names that begin with the letter 'S' for a focused segment, you can use the % wildcard. Here’s what that looks like.

SELECT "ssot__FirstName__c", "ssot__LastName__c"
FROM "ssot__Individual__dlm"
WHERE "ssot__LastName__c" LIKE 'S%'

Order and Limit Your Results

Even after filtering with WHERE, a query can still return thousands of records. To make the results manageable for review and to target specific records, you use the ORDER BY and LIMIT clauses.

Sort Data with ORDER BY

The ORDER BY clause sorts your result set based on one or more specified fields. This is crucial because, without it, the order of the results can be inconsistent.

• Ascending order: To sort in ascending order (A-Z, 1–100), use the ASC keyword.
  
• Descending order: To sort in the reverse order (Z-A, 100–1), use the DESC keyword.
  

If you wanted to find the 20 newest individuals (sorted by the date they were created in the source system), you would sort by the ssot__createdDate__c field in descending order.

SELECT "ssot__FirstName__c", "ssot__CreatedDate__c"
FROM "ssot__Individual__dlm"
ORDER BY "ssot__CreatedDate__c" DESC

Control the Row Count with LIMIT and OFFSET

The LIMIT clause controls exactly how many rows are returned in the final result set. This is most often used with ORDER BY to find the "top N" records, such as the 10 most recent records or the 5 lowest prices.

To find the 20 most recent individuals, you combine the previous query with LIMIT.

SELECT "ssot__FirstName__c", "ssot__CreatedDate__c"
FROM "ssot__Individual__dlm"
ORDER BY "ssot__CreatedDate__c" DESC
LIMIT 20

The OFFSET clause is used in conjunction with LIMIT to skip a specified number of rows before starting to return the result set. For example, to retrieve the second page of 20 results, you would skip the first 20: LIMIT 20 OFFSET 20.

Now that you can effectively find the data you are looking for, you can start turning that data into metrics and business insights in the next unit. But before moving on, put what you learned into practice by following the instructions to complete the challenge in this unit.