Emojis - SQL in ICM InfoWorks - 13 Order By Clauses 📑

 13 Order By Clauses 📑

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 come after the WHERE, GROUP BY, or HAVING clause if any. For example:

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 isn't a WHERE, GROUP BY, or HAVING clause, it should come after the FROM keyword used to specify a table name:

SELECT node_id,ground_level FROM [All Nodes] 📂 ORDER BY ground_level

If there's no FROM keyword specifying a table, the ORDER BY clause should come after the INTO FILE keywords and the filename:

SELECT node_id,ground_level INTO FILE 'd:\temp\selection.csv' 🗂️ ORDER BY ground_level

You can even order the results of a query without any WHERE, GROUP BY, FROM, or INTO FILE:

SELECT nodeid,ground_level ORDER BY ground_level 📊

13.1 Sorting Ascending and Descending ⬆️⬇️ To sort in ascending or descending order, use the keywords ASC and DESC respectively. If neither keyword is specified, the sorting defaults to ascending.

SELECT node_id, ground_level ORDER BY ground_level ASC ⬆️

You can also sort based on more than one expression, for instance, 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 uses display precision (default is 2):

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 ⬆️

13.2 Restricting number of results 🔍 To limit the number of results, use the keywords TOP and BOTTOM.

The number of results can be capped at a specific number of items or a percentage. You can choose the top or bottom results, using either a variable or a specific number.

The percentage is calculated based on the percentage of objects to which the query applies. For example, if you have 100 nodes but only 10 are selected when the query runs, then SELECT TOP 50 PERCENT will select 5 objects, not 50. Similarly, if there's a WHERE clause, the percentage applies to the 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 ⬇️

The WITH TIES keyword allows you to extend a selection to include all objects with the same value in the 'ordered by' field as the last selected object:

SELECT TOP 10 WITH TIES ORDER BY ground_level DESC ⬇️

WITHOUT TIES can also be used, but it's not necessary as it's the default.

You can also use the keywords TOP and BOTTOM to limit the number of objects deselected, deleted, or that have values set:

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 🔄

13.3 Rank 🥇 You can use the 'rank' of an object, which is the position the object appears in the list. Rank can only be used with an ORDER BY clause and only on the right side in an assignment in a SET clause:

SET user_number_1 = rank ORDER BY ground_level DESC ⬇️

If there are tied values in the sort order, all objects with the same value in the sort fields will get the same rank:

SET user_number_1 = rank ORDER BY ground_level,chamber_roof 🔄

This means objects with the same ground level and chamber roof level will have the same rank. 🥇🥈🥉