Before digging into Intermediate topics, you may want to review the Beginning SQL article.
The ORDER BY clause is a list of field names or aliases. Each field in the list is sorted ascending from low-to-high (alphabetical) unless the sort order is specified. To sort reverse-alphabetical or high-to-low, use the DESC keyword after the field alias or name.
The sort is the last thing applied by the database engine after all calculations have been made so a comma-delimited list of field aliases is all that is needed to sort the results efficiently. To sort by a calculated field in your field list, you can use the field alias.
ORDER BY Display_Name, Date_of_Birth DESC
In the Advanced Search Tool, the keywords "ORDER BY" are omitted 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 (below).
A NULL is never equal to anything. This can result in various oddities and unexpected behavior if not handled properly. Here are several corollaries to this rule:
This is why NULL has a special operator for comparison:
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, any Contacts without a Marital Status will be omitted. This is because NULL is never equal (or unequal) to anything else!
In order 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
You can accomplish the same thing using an OR statement, but it is more complex and the Advanced Search tool is unable to parse the SQL. In the case of a simple ISNULL, the tool is able to parse and display it as shown here:
See Also: Advanced SQL - Joins.
The Advanced Search tool adds each filter field with an "AND" operator. The AND can be thought of as exclusive rather than inclusive. For example, the search "male AND married AND over 40" will return a smaller set of records than "male OR married OR over 40." The second example will return 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 two different field values are to be included and they can not be combined using 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, ISNULL is used to return zero for age when Date_of_Birth IS NULL:
(ISNULL(Contacts.[__Age],0) < 18 OR Contacts.Household_Position_ID = 2)
Another example is returning the results for more than one wildcard using LIKE:
(Ministries.Ministry_Name LIKE 'Care%' OR Ministries.Ministry_Name LIKE ''%Groups')
When a series of unique values for a single field are to be included, it is better to use the IN clause. See Beginning SQL: IN.
When a range is need, it is better to use the BETWEEN clause. See Beginning SQL: BETWEEN.
Although the Advanced Search tool allows you to enter dates, 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 7 days in the past):
For more date functions, see Dynamic Dates and SQL Functions.
