Article #16147: SQL: Sorting on a Calculated Column

 Technical Information Database

TI1147D.txt   SQL: Sorting on a Calculated Column
Category   :Database Programming
Platform    :All
Product    :Delphi  All

Description:
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



Reference:


7/16/98 4:34:03 PM
 

Last Modified: 01-SEP-99