The 'Order By' clause is used to determine the order in which lines are initially displayed in grids and in which they are output to CSV files when SELECT clauses are used, with or without GROUP BY clauses, to output data.
The ORDER BY clause must follow the WHERE, GROUP BY, or HAVING clause if any. e.g.
SELECT COUNT(*) GROUP BY material, network.name HAVING COUNT(*) > 10 ORDER BY Count(*)
SELECT node_id,ground_level WHERE node_type = 'F' ORDER BY ground_level If there is no WHERE, GROUP BY or HAVING clause, it must follow the FROM keyword used to specify a table name, if any:
SELECT node_id,ground_level FROM [All Nodes] ORDER BY ground_level
If there is no FROM keyword specifying a table, the ORDER BY clause must follow the INTO FILE keywords and the filename:
SELECT node_id,ground_level INTO FILE 'd:\temp\selection.csv' ORDER BY ground_level
It is possible to order the results of a query without any WHERE, GROUP BY, FROM or INTO FILE:
SELECT nodeid,ground_level ORDER BY ground_level
Sorting ascending and descending
To sort in ascending or descending order, use the keyworkds ASC and DESC respectively. If neither keyword is specified, the sorting is ascending by default.
SELECT node_id, ground_level ORDER BY ground_level ASC
It is possible to sort based on more than one expression e.g. to sort by ground level and then by x coordinate in ascending order:
SELECT node_id, ground_level ORDER BY ground_level ASC, x ASC
For real numbers, sorting is by display precision (default 2) e.g.
SELECT node_id, ground_level,x ORDER BY ground_level DP 0 ASC, x ASC
SELECT node_id,ground_level,x ORDER BY ground_level DP 6 ASC, x ASC
Restricting number of results
To restrict the number of results, use the keywords TOP and BOTTOM.
The number of results can be limited to a given number of items and to a percentage. You can select the top or bottom from the results, and use a variable or a number.
The percentage is calculated as the percentage of the objects to which the query is applied e.g. if you have 100 nodes, but only 10 are selected at the point the query is run, and either the keyword SELECTED is used or the 'apply to current selection' check box is checked, then SELECT TOP 50 PERCENT will select 5 objects, not 50. Similarly, if there is a where clause, then the percentage applies tot he number of objects for which the WHERE clause is true.
SELECT TOP 5 node_id, ground_level ORDER BY ground_level DESC
SELECT BOTTOM 1.5 PERCENT node_id, ground_level ORDER BY ground_level DESC
Use the keywords WITH TIES to extend a selection to include all objects with the same value in the field being 'ordered by' as the last object selected. e.g.
SELECT TOP 10 WITH TIES ORDER BY ground_level DESC
The keywords WITHOUT TIES may be used, but are not necessary as this is the default behaviour.
It is also possible to use the keywords TOP and BOTTOM to restrict the number of objects deselected, deleted or that have values set. e.g.
DESELECT TOP 5 ORDER BY ground_level DESC
DELETE TOP 5 ORDER BY ground_level DESC
UPDATE TOP 10 SET user_number_1 = 123 ORDER BY ground_level DESC
Rank
It is possible to use the 'rank' of an object, where the rank is the position that the object appears in the list. Rank can only be used where there is an ORDER BY clause and can only appear on the right hand side in an assignment in a SET clause e.g.
SET user_number_1 = rank ORDER BY ground_level DESC
If there are tied values in the sort order, i.e. two objects have the same value in all the sort fields, then all objects with the same value will be given equal rank e.g.
SET user_number_1 = rank ORDER BY ground_level,chamber_roof
will result in objects with equal ground level and chamber roof level being given the same rank.