Blog Archives

As a recap, we have walked through clauses in a SQL SELECT statement in order to lead us up to this point where we are now ready to sort data.

Clause Name	Description	Status
WHERE	Filters the results to include only those rows that meet the specified condition.	CHECK
GROUP BY	Groups rows that have the same values in specified columns, often used with aggregate functions like SUM, AVG,MAX,MIN	CHECK
ORDER BY	Used to sort the result set in ascending or descending order

The ORDER BY clause in SQL is important for sorting the results of a query in either ascending or descending order. It enhances the readability and usefulness of your data by organizing it according to specific criteria.

There are a lot of nuances related to sorting and data types (integers, strings, dates) and the expected behavior for how data is sorted.

Data Type	Description	Example Values	Sort Order Example
VARCHAR	Variable-length character string	‘California’, ‘Florida’, ‘Texas’	ORDER BY column_name ASC (Alphabetical order)
NUMBER	Numeric data type, including integer and decimal	100, 200, 300	ORDER BY column_name DESC (Descending numerical order)
DATE	Date values	‘2024-02-01’, ‘2024-02-15’	ORDER BY column_name ASC (Chronological order)
TIMESTAMP	Date and time values, including time zone information	‘2024-02-01 08:00:00’, ‘2024-02-01 09:00:00’	ORDER BY column_name DESC (Reverse chronological order)
BOOLEAN	Boolean values (TRUE or FALSE)	TRUE, FALSE	ORDER BY column_name ASC (FALSE, then TRUE)

Sorting Salesforce Data

Typically, sorting is a highly requested function for any list or report as it provides sequencing and prioritization of information. Here are the typical cases why sorting is so powerful:

1. Sequencing and ranking by numeric values.
2. Ordering data in sequential order by date/time.
3. Organizing and grouping records together visually when they are not contiguous inside of your database.

Lightning list views and reports will allow you to sort by one column in descending and ascending order as it is presented to end users. Let’s explore how to apply this same sorting behavior to your data when you create a query.

Coding Order By 

ORDER BY is typically used at the end of a SQL query to sort the results by one or more columns. For example, SELECT * FROM Customers ORDER BY LastName ASC; sorts customers by their last names in ascending order.

The basic ORDER BY syntax is a follows…

ORDER BY [Field Name] [ASC | DESC ] 

Here is an example that we can run and explore together:

SELECT "COUNTRY",
"STATUS", COUNT(*) as "Lead Count",
COUNT (DISTINCT "STATE") "State Count"
FROM SFDC.STAGE.LEAD
GROUP BY "COUNTRY", "STATUS"
ORDER BY "COUNTRY";

As you can see by default the countries are sorted in ascending order. If you want to experiment you can try to apply the same sort by country in DESCENDING order. 

Your ORDER BY clause will look like the following:

ORDER BY “COUNTRY” DESC;

Next, let’s modify the ORDER BY field so we can see how data is sorted by changing the State Count:

 ORDER BY “State Count”;

Now, you can see the data is sorted in ascending order. Let’s see what that looks like if we sort the data in descending order:

ORDER BY “State Count” DESC;

Order by Multiple Columns

What if we want to order by country and then State Count? We can order our data and effectively sort it by multiple columns. This is a level of control you won’t have at your disposal without putting data into a tool like Excel. Understanding and using Order By in this way also sets the stage for more advanced analysis scenarios.

ORDER BY “COUNTRY” DESC, “State Count” ASC;