Skip to main content

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

Popular posts from this blog

Salesforce Lighting Migration: Plan for Success

In April 2018, I led the Salesforce Lightning Migration at Postmates. This is how I would do it over again if I could.

Define Goals The goals of a Lightning Migration are: Enable Lightning ExperienceConfigure Lightning ExperienceDocument all Salesforce processesCreate training and support materialsLightning Experience is adopted Easy enough right? Let's go though the plan to see how we accomplish each one. Enable Lightning Experience Just turn it on right? Well, kinda.
Although it may be controversial, I recommend turning it on and hiding the option from all users. This can be done by removing the Lightning Experience User permission from your custom profiles. 
And yes, turn it on in production
Why? You're going to turn it on anyway. Salesforce has declared this as the path forward, so you either turn it on now or wait until they flip the switch for you.
Plus, turning this on ahead of time will make deploying from a sandbox much easier. Trust me.
Goal #1 Enable Lightning Exp…

SOQL from the Command Line

SOQL and the Command Line go together better than peanut butter and jelly. Here's why.
Once upon a time, I got tired of running Salesforce Reports to get miscellaneous data. I found Workbench and started using their interface to run SOQL queries. This was great for a while, but I knew there was a better way.

Pro-tip with Workbench: If you set a browser bookmark after your query runs, you can return to that bookmark and it will re-run your query!

Enter the SFDX Command Line Interface (CLI). Get ready to take your productivity into hyperdrive! 🚀🚀🚀
sfdx force:data:soql:query If there's one command you learn, this is the one. All you have to do is pass a query string into the -q parameter and you're in business. Check it out:
-r | --resultformat This is my 2nd favorite thing. How often do you need to export a csv from Salesforce? And how annoying is it to make throwaway Salesforce reports each time? Yeah, it's the worst.
-r to the rescue! Just add "-r csv" to …

Why should I use SOQL?

You need data, fast!
Whether it's exporting a csv or answering a question, SOQL can get you the result much faster than creating a Salesforce report.

My top 3 reasons to use SOQL are:
Get data fastGet answers fastFlexible access Get Data Fast How often do you find yourself making a throwaway report to check what is happening in the system? For example, you updated your Lead routing and want to see if it's been assigning Leads as you expect. Well you could make a report, run it and review the results. Or you could run a SOQL query.
The benefits of running a SOQL query in this case is that you can retrieve different data sets much quicker. In a Salesforce report, you are stuck with the Report Type you selected when creating the report. With SOQL, you can traverse object relationships in a much more natural way. Get Answers Fast Have you ever been in a meeting and someone asks a data question that no one has the answer to? Well SOQL comes in handy here because you can run a quick …