ORDER BY

The ORDER BY clause is a list of field names or aliases. Each field in the list sorts ascending from low-to-high or alphabetical unless you specify the sort order.

To sort reverse-alphabetical or high-to-low, use ORDER BY with the DESC keyword after the field alias or name. This sorts the results in descending order.

The sort is the last thing applied by the database engine after making all calculations, so you only need a comma-delimited list of field aliases to efficiently sort the results. To sort by a calculated field in your field list, use the field alias.

ORDER BY Display_Name, Date_of_Birth DESC

The Advanced Search Tool omits ORDER BY keywords because the Platform includes this for you. You only have to enter the list of fields.

The ORDER BY clause has special relevance when used with the TOP N clause. See SELECT TOP N.

NULL

A NULL is never equal to anything. This can result in various errors and unexpected behavior if not handled properly. Here are several properties to this rule:

• A value is never equal to NULL.
• A value is never unequal to NULL.
• A standard join will omit NULL.
• NULL is not equal to NULL.

This is why NULL has a special operator for comparison:

• IS NULL
• IS NOT NULL

Consider the following filter: Contacts with Marital Status not equal to Married (ID 2). Here it is illustrated in the Advance Search Tool:

The filter clause looks like this:

Marital_Status_ID_Table.[Marital_Status_ID] <> 2

Because the "not equal" comparison is a comparison of equality, the results omit any Contacts without a Marital Status. This is because NULL is never equal (or unequal) to anything else!

To include Contacts without a Marital Status, you must convert NULL to a value using the ISNULL function:

ISNULL( Marital_Status_ID_Table.[Marital_Status_ID] ,0) <> 2

See Also: Advanced SQL - Joins.

AND vs. OR

The Advanced Search Tool adds each filter field with an AND operator. Think of AND as exclusive, not inclusive. For example, the search "male AND married AND over 40" returns a smaller set of records than "male OR married OR over 40." The second example returns nearly everyone. For this reason, OR is almost always used in a set of parentheses to group it with other criteria.

The OR operator is best used when you want to include two different field values and can't combine them with BETWEEN or IN.

For example, here is a query which returns anyone who is under 18 or has a Household Position of Minor. To include Contacts without a birthday, use ISNULL to return zero for age when Date_of_Birth IS NULL:

(ISNULL(Contacts.[__Age],0) < 18 OR Contacts.Household_Position_ID = 2)

Another example returns the results for more than one wildcard using LIKE:

(Ministries.Ministry_Name LIKE 'Care%' OR Ministries.Ministry_Name LIKE '%Groups')

When you want to include a series of unique values for a single field, use the IN clause. See Beginner SQL: IN.

When you need a range, to use the BETWEEN clause. See Beginner SQL: BETWEEN.

Dynamic Dates

Although you can enter dates in the Advanced Search Tool, it almost always makes sense to use dynamic dates rather than specific dates. A dynamic date uses built-in date functions to base the dates on the current time on the database server.

Here is an example of Contacts created after a date:

The original date can be replaced with a GETDATE function (in this case, we're using seven days in the past):

For more date functions, see SQL Functions.

See Also

• Views: Advanced Techniques
• View Examples