2961:SQL: Sorting on a Calculated Column
KEYWORDS: SQL DATABASE SORT CALCULATED AREA: Database Programming
At times, a given data schema will require that a data set will need to be
ordered by the result of a calculation. In Delphi applications using SQL,
this is possible, but the methodlogy varies slightly depending on the
database type used.
For local SQL involving Paradox and dBASE tables, the calculated field
would be given a name using the AS keyword. This allows the calculated
field to be referenced for such purposes as setting a sort order with an
ORDER BY clause in an SQL query. For example, using the sample table
ITEMS.DB:
SELECT I."PARTNO", I."QTY", (I."QTY" * 100) AS TOTAL
FROM "ITEMS.DB" I
ORDER BY TOTAL
In this example, the calculated field is designated to be referred to as
TOTAL, this column name then being available for the ORDER BY clause for
this SQL statement.
The above method is not supported for InterBase. It is still possible,
though, to sort on a calculated field in InterBase (IB) or the Local
InterBase Server tables. Instead of using the name of the calculated
field, an ordinal number representing the calculated field's position in
field field list is used in the ORDER BY clause. For example, using the
sample table EMPLOYEE (in the EMPLOYEE.GDB database):
SELECT EMP_NO, SALARY, (SALARY / 12) AS MONTHLY
FROM EMPLOYEE
ORDER BY 3 DESCENDING
While IB or LIBS tables require this second method and cannot use the
first method described, either of the two methods can be used with local
SQL. For example, using the SQL query for the Paradox table and adapting
it to use the relative position of the calculated field rather than the
name:
SELECT I."PARTNO", I."QTY", (I."QTY" * 100) AS TOTAL
FROM "ITEMS.DB" I
ORDER BY 3
TI
LiveJournal
PhotoFile