Sorting the Recordset

You may have noticed that the viewselected.asp page does not display the records in alphabetical order by family name as we would normally prefer. It's easy to modify the SQL query so that it sorts the contents of the recordset as the recordset is created. In fact we need to do more than sort by family name (text1) - that would not ensure that 'Smith, Anne' would appear before 'Smith, Bob' as you would expect. As you can see, we need to sought by family name (text1) and then also by first name (text2) - here's the modified SQL query that you can use in your viewselected.asp page. Try it!

Putting the SQL keywords in capital letters is a convention, but not a requirement.

If, for some strange reason, you want to sort the records in reverse (descending) alphabetical order you could use:

There is a restriction on sorting that you should be aware of. Do you recall that the generic database that I have created for you contains some text fields and also some memo fields, amongst others? A memo field can contain much more text than a text field but there is a catch: you cannot sort according to the contents of a memo field. For example, if were stored the family names in a memo field then we would not be able to sort alphabetically by family name.

Filtering to generate a recordset that contains only selected fields

We already saw in two previous pages (aspdatabase.htm and viewselected.htm) how you can filter out unwanted fields (columns) from the database by using something other than an asterisk after the word SELECT in the SQL query. If you use an asterisk then ALL the fields are copied into the recordset but if you use an SQL query such as "SELECT text1, text2 FROM main" then only the named fields are copied into the recordset - the other fields are filtered out. The order in which you list the fields in the SQL query is not important.

Filtering to generate a recordset that contains only the records that meet certain criteria

This section explains how to apply a filter when the recordset is created such as to exclude some of the records (rows) - this is not the same as excluding some of the fields (columns). You need to include the keyword WHERE in your SQL query if you want to apply a filter that excludes unwanted records from the recordset.

Here are some examples of SQL queries that include filters:

If we wanted to create a recordset that includes only people with family name "Smith" then we could use:

In the above query, note that if we are comparing the field value to a string or a date then the string or date must be enclosed in single (not double) quotes.

Here's a query to extract records whose text2 (first name) value begins with "M".

The % character here acts as a wildcard representing any string of characters.

To extract records whose text1 (family name) value begins with a letter between L and S

For the next few examples, let's try applying filters to the number field called num1 which is in the generic database that I have given you. This number might represent a price, for example.

To extract records whose num1 value is less than 50:

You could also let the user determine the maximum price - let's assume that you've used a form for this and that the user's number has been placed in a variable called MaxPrice then the query would become:

As you can see, there are many ways to filter the records and therefore SQL queries can be quite sophisticated and powerful - we have only touched on SQL here but there are whole books (and websites) devoted to Structured Query Language if you want to learn more...