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. 🥇🥈🥉

Emojis - SQL in ICM InfoWorks - 12 Explicit Select Clauses and Group By Clauses 📊

 12 Explicit Select Clauses and Group By Clauses 📊

The purpose of an explicit select clause is to generate a grid 📈 or CSV file 📄 of values with one row in the grid or file for each object meeting given criteria.

'Group by' clauses 🧮, on the other hand, allow you to calculate totals, averages, maxima, minima etc. for groups of objects e.g. by system type, length, diameter. The objects can be grouped by one or more fields, variables, expressions or some combination of these. These can also be displayed in grids or output to CSV files 📄.

It is also possible to store the results of a 'group by' clause in a 'table variable' 📑 with the aim of then using those results in a further select clause known as an implicit join.

As described later, it is possible to perform an implicit GROUP BY in which the totals, averages etc. are calculated for all objects in the table meeting given criteria. These clauses look very similar to explicit select clauses. The means by which the two are distinguished will be described below.

12.1 Explicit Select Clauses 📝 An explicit select clause may be used to generate grids or CSV files containing one or more values for each object meeting given criteria.

The main advantage of using an explicit select clause over other ways of displaying data in the software is that you can display the results of calculations, including calculations on one-to-many links and array fields.

An explicit select clause consists of the following sub-clauses, most of which are optional.

• A SELECT sub-clause containing the expressions to be output to file or grids within the software. The SELECT sub-clause is compulsory. There can be one or more expressions separated by commas, these are termed ‘sub-expressions’ 📜
• A FROM sub-clause specifying the table from which the data should be extracted. This sub-clause is optional 📤
• An INTO sub-clause specifying the file into which the results should be output. This sub-clause is optional 📁
• A WHERE sub-clause specifying a condition which should be fulfilled before the object will be considered and included in the grid or file 🔍

If no FROM sub-clause is specified, the current table will be used 🔄.

If no INTO sub-clause is specified, the results will be displayed in a grid 📈.

If no WHERE sub-clause is specified, all the relevant objects will be considered 🌐.

The keyword DISTINCT can be used with SELECT to select a group of values without duplicates e.g.

SELECT DISTINCT system_type 🚫

will display a grid of each system type in the table.

DISTINCT can be used in conjunction with WHERE and ORDER BY, but not GROUP BY, HAVING, TOP or BOTTOM.

Where multiple fields are selected, the query returns one line for each distinct set of values, rather than the distinct values for each field.

12.2 Group By Clauses 🧮 The 'Group By' clause consists of the following sub-clauses, most of which are optional.

• A SELECT sub-clause, as described above. As with the explicit select clause, this is compulsory 📜
• A FROM sub-clause, as described above. This sub-clause is optional 📤
• An INTO sub-clause, as described above. This sub-clause is optional 📁
• A WHERE sub-clause, as described above. This sub-clause is optional 🔍
• A GROUP BY sub-clause listing the fields, expressions, variables etc. used to group the objects. There can be one or more fields, expressions etc. separated by commas, these are termed ‘sub-expressions’ 📊
• A HAVING sub-clause specifying a condition for the group (rather than the object) which should be fulfilled for the data for this group to be output 🔍

If no FROM sub-clause is specified, the current table will be used 🔄.

If no INTO sub-clause is specified, the results will be output to a grid within the software 📈.

If no WHERE sub-clause is specified, all the objects will be considered 🌐.

If no GROUP BY sub-clause is specified, all the objects in the table will be considered as one group (this is known as an ‘implicit GROUP BY’) 🌎

If no HAVING sub-clause is specified, all the groups will be output to the file or grid 📁.

It is possible to use a blob field in a GROUP BY query e.g.

SELECT SUM(COUNT(details.*)) GROUP BY details.code 📊

When the result of the GROUP BY clause is output to a file, the values for all the sub-expressions in the GROUP BY sub-clause and the SELECT sub-clause will be output to the file. The records output will be sorted in an order determined by the sub-expressions in the GROUP BY clause.

It is possible to alter the text used in the header of the CSV file or the grid for each expression within the SELECT sub-clause by using the keyword AS e.g.

SELECT COUNT(*) AS MyCount, MAX(x) AS MaxX Group BY status

Note that the aliases are not strings, they do not appear in quotation marks. If you want to have spaces in the names you should deal with them as with variables with spaces in the name and use square brackets e.g. [My Title].

The aggregate functions described above, with the exception of FIRST and LAST may be used in the sub-expressions in the SELECT sub-clause. It is also possible to use the special sub-expression COUNT(*), which will return the number of objects in each group.

12.3 Implicit Group By Clause 📊 It is possible to write a query which works like a GROUP BY clause but instead of grouping things by one or more fields or expressions, considers all the objects at once. A simple example of this would be a query that counts the number of objects in the table e.g. SELECT COUNT(*) 📝

This is different from an explicit select clause e.g. SELECT node_id 📜

The rule for determining whether a query is an implicit group by or a select acting on individual objects is as follows:

• If there are no aggregate functions e.g. SELECT node_id,x,y then the query is clearly NOT an implicit GROUP BY ❌
• If there are aggregate functions acting on the results of aggregate functions e.g. SUM(COUNT(details.*)) then it clearly IS an implicit GROUP BY ✅
• If neither of the above apply then more detailed analysis has to be performed on each aggregate function as follows 🔬

If the aggregate function contains an array field or a one-to-many link followed by an asterisk e.g. SUM(details.), ANY(us_links.), then the aggregate function must be intended to work at an object level since this would not be valid at a group level.

If the aggregate function contains a field for an array field or a one-to-many link followed by an asterisk e.g. COUNT(details.code=’Z’), MAX(us_links.width<150), then the aggregate function must be intended to work at an object level since this would not be valid at a group level.

Emojis - SQL in ICM InfoWorks - 14 Prompts 🖊️

 14 Prompts 🖊️

To allow the user 👤 to enter parameters used in queries, the statements

PROMPT LINE 📜

PROMPT TITLE 🏷️

PROMPT DISPLAY 🖥️

are used.

The fundamental way this works is as follows:

A number of PROMPT LINE statements define the list of values to be input or displayed and their formats. There will be one line displayed in a grid 📊 for each value.

The variables must be scalars. The variable can either have been defined and set before the PROMPT LINE statement or mentioned for the first time in the prompt line statement.

Valid values used in the prompt line statement are as follows, they will be explained in detail below:

PROMPT LINE <variablename> 📋

PROMPT LINE <variablename> '<description>' 📝

PROMPT LINE <variablename> '<description>' STRING 🧵

PROMPT LINE <variablename> '<description>' DATE 📅

PROMPT LINE <variablename> '<description>' DP <numberofplaces> 🔢

PROMPT LINE <variablename> '<description>' BOOLEAN ☑️

PROMPT LINE <variablename> <variablename> 🔄

PROMPT LINE <variablename> <variablename> STRING 🧵

PROMPT LINE <variablename> <variablename> DATE 📅

PROMPT LINE <variablename> <variablename> DP <numberofplaces> 🔢

All of these but the first may optionally be followed by

LIST <listvariablename> 📜

FILE 📂

FOLDER 📁

MONTH 🗓️

RANGE <startvalue> <endvalue> 📏

In general PROMPT LINE is followed by the variable name and the description. If the description is omitted as in the first case, or if the description is the blank string '', the description will be the variable name. The description may be a scalar variable which must have been defined before the PROMPT LINE clause. This is evaluated when the dialog is displayed.

Examples of valid prompt lines:

PROMPT LINE $x 📋

This defines a number variable which will have the default of 2 decimal places in the grid and the description '$x'. This takes advantage of the special case where numeric variables can be defined by simply saying PROMPT LINE $x without having to give a description.

PROMPT LINE $s '' STRING 🧵

This defines a string variable. Note that because we want to use more than 3 parameters we have to add a description field for the variable, but we can take advantage of the special case where using the blank string '' will cause the variable name to be used as the description.

PROMPT LINE $d '' BOOLEAN ☑️

This defines a boolean variable. The value will be displayed as a check box; checked for true, unchecked for false.

PROMPT TITLE '<title>' may be used to change the title of the row displayed from the default, which is 'SQL Prompt Dialog'. The title may also be a scalar variable, which will be evaluated at the point the dialog is displayed.

If you wish the entire grid to be read-only so that the values can be looked at by the user but not altered, use the keyword READONLY following PROMPT DISPLAY i.e. say

PROMPT DISPLAY READONLY 🔒

This would typically be used to display the results of SELECT queries storing values in scalars and scalar expressions (see below).

Error messages that may occur with the prompt functionality are as follows:

PROMPT LINE clause too short - the clause is too short e.g. PROMPT LINE ❌

PROMPT LINE clause requires a variable after the LINE keyword e.g. PROMPT LINE 23 - self explanatory ❓

PROMPT LINE clause variable not a scalar - the variable has already been used in another context (currently list or array) e.g. SET $x = x; PROMPT LINE $x; ⚠️

PROMPT LINE clause description not a string - the description field in the 4th parameter is not a string e.g. PROMPT LINE $x STRING (in this case perhaps you have forgotten to add a description after the variable name). 🚫