Whenever you need to add custom SQL using System Setup > Page Views page, it is often helpful to begin the View on the page where the View exists. Then save the View and go to System Setup > Page Views to edit the View. You should be aware that often once you enter custom SQL into the setup page, that SQL is not editable in the "Form Layout" mode.
For example, a filtered page called "My Opportunities" could be created such that users with access to that page would only see the Opportunities for which they are the contact person. The filter clause for such a page is:
Contact_Person_Table.User_Account = dp_UserID
The view will automatically sort by the first column unless you specify a sort order. To specify a sort order, list the field names in a comma delimited list. If you are using a subquery, use the alias assigned in the field list.
In SQL, fields may be used for filters without appearing in the results. This is accomplished by omitting the field in the SELECT clause. Notice Date_of_Birth is in the WHERE clause but not in the SELECT clause:
SELECT Contact_ID, Display_Name FROM Contacts WHERE Date_of_Birth IS NOT NULL
In the Advanced Search Tool, this is accomplished by deleting the field from the Field List in the SQL Layout tab:
The result in the Advanced Search Tool is a grayed-out field with a comparison.
To use the same field list as the page (All Record view), you can clear the Field List in the SQL Layout: