DBSQLSORT(database, table, sort, field_name, rec_num)

The DBSQLSORT function returns the value of a database SQL query that applies a sort order.

database is the name of a database and must include the full path if not in the same directory as the open document.

table is the name of the table within the database. table could also be the name of a relation.

sort is a text string containing an SQL sort statement. This is a field name or names with an optional "DESC" to reverse the default ascending order.

field_name is optional and can be either the name of the field within a table or relation or the column number. If omitted, every row and column of the filtered table is returned.

rec_num is optional (and cannot be specified if field_name is omitted) and determines the record or row number of the returned value. If omitted, the entire column is returned (or table if field_name is also omitted).

The function arguments are case insensitive.

For example, a database called wineshop, located in a folder c:\ability\samples, contains a Product table of wines. Products contains three fields as follows:

Title

Price

Classification

Château Haut du Puy

17.00

red

Volnay-Santenots

27.00

red

Meursault-Charmes

54.00

white

Le Chambertin

53.00

red

Chambolle-Musigny

28.50

red

DBSQLSORT can be used to obtain the following results:

DBSQLSORT("c:\ability\samples\wineshop.adb", "price DESC")

returns "Meursault-Charmes", the first column of the first record of the table when sorted by Price in descending order.

DBSQLSORT("c:\ability\samples\wineshop.adb", "price", 2, 3)

returns 27.00, the second cheapest wine price.

Note that:

DBSQLSORT(database, table, sort, column, record)

is equivalent to using DBSQL (see DBSQL) as follows:

DBSQL(database, "SELECT * FROM <table> ORDER BY <sort>", column, record)

See also:

Other remote functions