Selecting records from an Access database is the most frustrating thing. They are input to the database in the order they are entered. Even if you use sort by in the access environment to change the record view, the record order in the data table has not changed. If you are using asprecordset to write records on the web page, you may know that the order of disorder is so painful. However, you may have to face this problem frequently, because there is no simple and convenient solution. Fortunately, order by can simplify this problem. To sort your results, you only need to add order by at the end of the SELECT statement and specify the reference columns to be sorted. Therefore, if you want to sort the MERs table by 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" In this way, as long as you create a recordset and start to write the results to the screen, you will see the data in alphabetical order. Multi-level sorting
In fact, it is not only possible to perform level-1 sorting in SQL statements. In fact, in many cases, you may want to specify two to three levels of deep data sorting.
Suppose you have a data table.
The previous single-level order by sorting is used to retrieve data in the following order:
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, order by plays an appropriate role. In the actual table structure, absurdly assured is the last entry, but it is placed at the top of the search result. Hands on records are listed last because o is listed at the end of the alphabet. Obviously, absolutely is better placed before absurdly according to the alphabet. To solve this problem, you need to adopt the order by sorting standard of level 2nd, and sort by Column 2nd:
SQL = "select c_lastname, c_firstname, c_email from customers order
C_lastname, c_firstname" The result is first sorted by the c_lastname column and then by the c_firstname column. If your data table contains a large number of records, designing and sorting carefully will make the output result arrangement more reasonable. put into use if you like the majority of Program Members, Code , indulge in the enthusiasm for new technologies. Why don't I try SQL encoding from the lengthy ASP encoding? Next we will discuss the common problems of ASP programming and how to efficiently use SQL statements in ASP. |