Sometimes clients want to have sorting of variable (user-selectable) directions.
One way to achieve it would be to transcode some field (or fields-based expression) to a number (maybe that is already a number, or maybe it is for example timestamp, so can be reduced to number using something like DATEDIFF), and then to multiply it by SQL parameter, with parameter then being either +1 or -1 or 0 ( direct sort, reverse sort, no sort )
The database can have ascending and descending indexes by the said expression (or a bi-directional index if Firebird would have one implemented).
The thing is to recognize that the SQL parameter is de-facto a constant value and then to build fetching plan using either ascending or descending index, or none at all, based upon the value of the parameter.
This is a special case, so no "general" problems should emerge.
- multiplication of expression and numeric parameter in ORDER BY clause
- existing index by that expression
- the numeric parameter value being +1 or -1 or 0 (actually, any non-zero number there can be substituted with +1 or -1 for the sake of sorting)