SELECT – Order By

This statement sorts the selected rows by the fields specified.

Syntax

SELECT * FROM tablename ORDER BY fieldname [ASC | DESC]

Parameters

fieldname

Specifies the name of the field used to sort the rows.

Remarks

If fieldname is an indexed field, then the table is opened with the sort properties for index active. ADOCE does not support sorting on binary fields. You can sort on up to four fields.

When fieldname has no associated index, an in-memory sort is done on the data and an ordered recordset is returned.

The ASC tag sorts the data in ascending order, while DESC sorts it in descending order. DESC is the default sort order.

Return Values

One of the following error values can be returned:

• E_OUTOFMEMORY
• DB_E_ERRORSINCOMMAND
• DB_E_NOTABLE
• DB_E_BADCOLUMNID

Example

Assume there is a database called books with the following fields: title, price, and author.

Dm rs
Set rs = CreateObject("adoce.recordset")
rs.open "select * from books order by author ASC, price DESC"

The following code example shows how to accomplish the same effect by creating indexes on the fields you want to sort by.

Dim rs
Set rs = Create Object("adoce.recordset")
rs.open "create index authorindex on books (author)"
rs.open "create index priceindex on books (price DESC)
rs.open "select * from books order by author, price DESC"