Each
time we’ve retrieved the contents of a table, we’ve received the result
in the order in which they were entered into the table. What if you want to sort
the contents of the table, through? Imagine that you have a table called portfolio,
which has information about your personal stock portfolio. This table contains
columns such as price, shares, and symbol. Wouldn’t it be neat if you could
list the contents of this table but have it sorted by price or shares?
Imagine
that your portfolio table contains the following column:
·
Stock ID – A Unique , numeric Key · Symbol – The Stock ticker
symbol · Price – The Price the stock was purchased at ·
Shares – The total number of shares purchased · Date – The
date the transaction occurred
If
your portfolio of 10 stocks, there would be 10 rows in the portfolio table. If
you were to use code similar to that in Listing to display the contents of the
portfolio table, the order you would output the data would be the order in which
you entered the rows into the table.
You can sort the Record set; however,
you have to use a scrollable database cursor on the client’s machine. This
is not the most efficient technique, especially if you are dealing with a large
portfolio. Tomorrow, we’ll look at a way to sort results from a record set
using forward-only cursors on the server, a method that much more efficient.
To sort a Record set you need to set the Sort property to the name of the
column that you want to sort by after opening the record set object. For example,
if you wanted to sort the price column, you would issue the command.
ObjRecordsetInstance.Sort
= “Price”
After
executing the open method. When using the sort property, you need also to set
the cursor location property to ad Use Client. When using client-side cursors,
you need to use the ad Open Static cursor, so be sure to reference that in the
Record set object’s open method.
The sort method sorts the results
by a particular column in ascending order by default. To specify that you want
to sort a column in descending order, append the keyword DESC to the column you
are sorting. For example, if you wanted to sort by the shares column in descending
order, you would need to set the sort property as follows:
ObjRecordsetInstance.sort
= “Price DESC”
If
there is a tie between two value in the column you are sorting on, you can specify
a secondary column to use to resolve the tie. To specify a secondary column to
sort by, use the following syntax:
The
contents of the Portfolio table. The sort property is used to sort the result
based on two column: First, the number of shares is sorted in descending order;
if there is a tie that column among a set of records, those records are sorted
based on their price in ascending order.
