ORDER BY
This function sorts columns in ascending order (the default) or descending order (if specified, as shown in the example below).
Syntax
ORDER BY columnA [ASC | DESC] [NULLS FIRST | NULLS LAST] [, columnB [ASC | DESC] [NULLS FIRST | NULLS LAST], ... ]]
Remarks
• If you do not specify ORDER BY, the order is undefined. Without ORDER BY, the sort order can be different with two runs of the same SQL query.
• When you specify multiple columns, the results are sorted by the first column specified, then by the second column within the first column, and so on.
• By default, the TDV Server returns NULLs first for ASC and NULLs last for DESC.
— Microsoft, Sybase, SQL Server, MySQL and Informix data sources also use these default values.
— Oracle and DB2 data sources use opposite defaults.
• TDV supports ORDER BY in analytical functions as well as SELECT clauses.
Note: Oracle and Netezza also support ORDER BY in analytical functions. Microsoft data sources do not.
Example (ORDER BY without a Function)
SELECT *
FROM /shared/examples/ds_inventory/inventorytransactions InventoryTransactions
ORDER BY ProductID, UnitsSold DESC
This example selects all columns from the inventorytransactions table, sorts them by ProductID (in ascending order), and within each ProductID sorts them by UnitsSold (in descending order).
Example (ORDER BY with Columns Specified by Ordinal Position)
The order that the columns are selected can be replaced by the integer that represents the ordinal position where the SELECT occurred. If all columns of a table are selected by SELECT *, the column position in the table (expressed as an integer) can be used.
SELECT ProductId, UnitsSold, UnitPrice
FROM /shared/examples/ds_inventory/inventorytransactions InventoryTransactions
ORDER BY 2 DESC, 1
This example selects the three columns ProductId, UnitsSold, and UnitPrice from the inventorytransactions table, and orders the results first by UnitsSold, in descending order, and then by ProductId, in ascending order.
Example (ORDER BY with a Multiplication Function)
SELECT ProductId, UnitsSold * UnitPrice
FROM /shared/examples/ds_inventory/inventorytransactions
ORDER BY ProductID, UnitsSold * UnitPrice DESC
This example selects ProductId, UnitsSold, and UnitPrice from inventorytransactions and sorts them by ProductID in ascending order, and within each ProductID sorts them in descending order of the results obtained by multiplying UnitsSold by UnitPrice.