Statement selecting records from an Access database is one of the most frustrating things to do in the order in which they are entered in the database. Even if you use sort by to change the view of a record in an access environment, the order of records in the datasheet does not change.
If you're using Asprecordset to write records on a Web page, you probably know how painful the order of jumbled is. But you may have to face the problem often, because there is no simple and easy solution. The good news is that the order by can simplify the puzzle.
To sort your results, simply add an order by to the end of the SELECT statement, and then specify the reference columns you want to sort. Therefore, if you want to sort the Customers table according to the customer's last name, you can write the following query statement:
SQL = "Select C_lastname, C_firstname, c_email from Customers order by C_lastname"
So, as soon as you set up the recordset and start writing the results to the screen, you'll see that the data is sorted in alphabetical order.
Multilevel sorting
In fact, not only can the SQL statements in the first order. In fact, in many cases, you might want to specify two to three levels of data ordering. Suppose you have the following datasheet, which reads as follows: [Image003.gif]
The previously used single order by sort takes out data in the following sequence:
Absurdly assured
Absurd@assured.com
Absolutely assured
Absolutely@assured.com
Crazed coder
Crazy@coder.net
Loosely Fringe
Loose@fringe.to
Lunatic Fringe
Lune@fringe.to
Hands on
hands@yes.org
Obviously the order is acting as it should. Under the actual table structure, the absurdly assured is the last entry, but it is ranked at the top of the search results. Hands on record last because O is at the end of the alphabet in the list above. Obviously, the absolutely is best ranked before absurdly by the alphabet. To do this, you need to take the 2nd level order by sort criteria and refer to column 2nd:
SQL = "Select C_lastname, C_firstname, c_email from Customers
C_lastname, C_firstname "
The results are sorted first by the C_lastname column and then by the C_firstname column. If your datasheet contains more records, careful design of the sort will make the output layout more reasonable.
Put into use
If you like most programmers, you love to make your own code and indulge in the frenzy of mastering new technology. Why don't you try the SQL code from an ASP's lengthy code? Here we will discuss the common problems of ASP programming and how to make efficient use of SQL statements in ASP.
