SOQL Basics - WHERE, ORDER BY and LIMIT

WHERE, ORDER BY and LIMIT are three of the most commonly used techniques in SOQL. In this post we'll dive into them one at a time and look at some examples.

WHERE

When you need to filter records, you can use a WHERE clause to define which records should be returned. You can use the AND, OR and NOT operators to define your filter. If you need to use multiple filters you can use parentheses to control how the filters are applied.

Let's look at some examples:

This query will return all Opportunities with an Amount greater than $50,000.

This query will return all Leads with a Lead Source of Web that were created This Week. The THIS_WEEK keyword is an example of a Date Literal and makes filtering on date and datetime fields much simpler.

This query will return all Accounts in CA, NY or FL based on Billing State. The IN keyword allows you to filter for records that have any of the values you define. Using NOT IN is a common technique and it has the opposite affect - returning records that do not have any of these values.

ORDER BY

Sorting your results is especially important when viewing or exporting data. SOQL allows you to sort by using an ORDER BY clause. You have the ability to sort many fields ascending or descending and to specify if null values should be shown at the beginning or end of your result.

Let's look at some examples:

This query will return all Accounts in alphabetical order from A to Z (ascending).

This query will return all Contacts by Account Name ascending, then First Name ascending. Notice that the ASC keyword was left off. This works because ascending is the default sort order.

This query will return all Opportunities by Amount from high to low (descending) with null values at the end.

LIMIT

It's common to query and only need to return a certain number of results. I also find myself adding LIMIT 10 to the end of my queries when I'm testing them out just to see if I'm getting the results I'm expecting.

Let's look at some examples using what we've learned:

This query will return the top 5 Opportunities by Amount.

This query will return the first 10 Accounts in alphabetical order that are in CA, NY or FL based on Billing State.

This query will return the last 10 Leads created. The format(CreatedDate) will localize the datetime field, making it easier to read. It will localize other data types like number, date and currency too! More info is in the docs.

Continued Learning

SOQL can be tough to grasp for those that haven't used it before. I know I was confused by it at first, but it just takes practice. I recommend tweaking the queries above until you understand what they're doing.

If you've never used SOQL before and are unsure of the benefits, I wrote a whole other article on why you should use SOQL. Check it out if you're on the fence.

Here are some helpful resources for getting started with SOQL: 

Comments

  1. This was huge information for all ,those who needed these type article. This was really good and of course knowledgeable. Thank you for sharing this much information with us.Mexico Import Data

    ReplyDelete

Post a Comment

Popular posts from this blog

Joins in SOQL

Modify Salesforce Data from the Command Line

SOQL from the Command Line