SELECT Clauses generating grids and files

As well as being used to select objects in the networks, SELECT clauses can be used to generate grids of data displayed in the software and CSV files. There are three major types of these clauses:

1. SELECT clauses returning one grid line or line in a file per object in the network or per object satisfying the criteria in the WHERE sub-clause - these are termed 'explicit select clauses'.
2. SELECT clauses with GROUP BY sub-clauses returning one grid line or line in a file per group of objects containing totals, averages, maxima, minima, counts etc. aggregated over all the members of the group. The objects can be grouped by one or more fields, variables, expressions or some combination of these.

3. SELECT clauses without a GROUP BY sub-clause which behave as 2 above but with the totals, maxima etc. aggregated over everything in the network, or everything in the network satisfying the criteria in the WHERE sub-clause). These clauses look very similar to explicit select clauses. The means by which the two are distinguished will be described below.

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.

1. 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’
2. A FROM sub-clause specifying the table from which the data should be extracted. This sub-clause is optional.
3. An INTO sub-clause specifying the file into which the results should be output. This sub-clause is optional.
4. A WHERE sub-clause specifying a condition which should be fulfilled before the object will be considered and included in the grid or file.
5. An ORDER BY sub-clause specifying the order the objects should appear 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 .

‘Group by’ clauses 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.

The ‘Group By’ clause consists of the following sub-clauses, most of which are optional.

1. A SELECT sub-clause, as described above. As with the explicit select clause, this is compulsory.
2. A FROM sub-clause, as described above. This sub-clause is optional.
3. An INTO sub-clause, as described above. This sub-clause is optional.
4. A WHERE sub-clause, as described above. This sub-clause is optional.
5. 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’.
6. 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.
7. An ORDER BY sub-clause specifying the order the objects should appear 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 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.

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.

It is possible to restrict the number of records displayed or written to the file by use of the TOP and BOTTOM keywords.

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:

1. If there are no aggregate functions e.g. SELECT node_id,x,y then the query is clearly NOT an implicit GROUP BY
2. 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
3. If neither of the above apply then more detailed analysis has to be performed on each aggregate function as follows:
   1. 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.
   2. 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.

Since the values returned by these queries are for the whole network or selection, it is possible to assign the values to scalar variables by using the INTO keyword e.g.

SELECT COUNT(*) INTO $mycount

It is possible to assign more than one value into a variable with one clause e.g

SELECT MAX(x) INTO $mymaxx,MIN(y) INTO $myminy

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

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

It is possible to use an array field in an explict SELECT clause e.g.

SELECT us_node_id,ds_node_id,link_suffix,details.code,details.remarks WHERE details.code = 'SA' and details.remarks IS NOT NULL

This will generate one report line for each 'record' in the array.

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 COUNT(*) GROUP BY material, network.name 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\badger.csv' ORDER BY ground_level

It is, of course, possible to order the results of a query without any WHERE, GROUP BY, FROM or INTO FILE e.g.

SELECT node_id,ground_level ORDER BY ground_level

It is possible to sort ascending and descending by using the keywords ASC and DESC respectively

SELECT node_id,ground_level ORDER BY ground_level ASC

SELECT node_id,ground_level ORDER BY ground_level DESC

If nether keyword is specified then the sorting is ascending by default.

It is possible to sort based on more than one expression e.g.

SELECT node_id,ground_level ORDER BY ground_level ASC, x ASC

which sorts by ground_level ascending, then if two or more nodes have the same ground level, sorts by the x coordinate ascending.

It should, of course, be borne in mind that this makes most sense for strings and integers, for real numbers the sorting is by the 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

SELECT node_id,ground_level,x ORDER BY ground_level ASC, x ASC

Will all potentially give different results.

It is possible to restrict the number of results by using the keywords TOP and BOTTOM

You can limit the number of results to a given number of items and to a percentage, you can select the top or bottom from the results, and you can use a variable or number.

The percentage is calculated 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 to the number of objects for which the WHERE clause is true.

When using TOP and BOTTOM it is possible to use both sorts of select, the variety where a grid or file is produced and the variety where the objects are selected in the network.

SELECT TOP 5 node_id, ground_level ORDER BY ground_level DESC

SELECT TOP 1.5 PERCENT node_id, ground_level ORDER BY ground_level DESC

SELECT TOP 3 PERCENT node_id, ground_level ORDER BY ground_level DESC

SELECT BOTTOM 5 node_id, ground_level ORDER BY ground_level DESC

SELECT BOTTOM 1.5 PERCENT node_id, ground_level ORDER BY ground_level DESC

SELECT BOTTOM 3 PERCENT node_id, ground_level ORDER BY ground_level DESC

LET $val = 3; SELECT TOP $val PERCENT node_id,ground_level ORDER BY GROUND_LEVEL desc

You might perhaps think the fact that selecting the objects with the highest five values for a given field requires you to say ORDER BY blah DESC is counter-intuitive, but this particular usage is 'standard' SQL.

It is possible to select any objects with the same values from the fields being 'ordered by' by using the keywords WITH TIES e.g.

SELECT TOP 10 node_id, ground_level WITH TIES ORDER BY ground_level DESC

The essential purpose of this is to avoid the case where a number of objects are selected but actually there are objects with exactly the same characteristics with regard to the sorting criteria which aren't selected e.g. if the ordering is by a 'score' then the selection is extended so that all objects with a score similar to that of the last object originally selected are included in the selection.

The keywords WITHOUT TIES may be used, although they are not necessary as this is the default behaviour.

If SELECT is used without a list of fields but in conjunction with TOP or BOTTOM and GROUP BY, then that number of objects will be selected e.g.

SELECT TOP 5 ORDER BY ground_level DESC

It is also possible to deselect objects, when DESELECT is used in the same was as SELECT but without a list of fields e.g.

DESELECT TOP 5 ORDER BY ground_level DESC

More drastically, it is possible to actually delete objects in this way e.g.

DELETE TOP 5 ORDER BY ground_level DESC

It is possible to restrict the objects that have values set with a SET clause in the same way e.g.

UPDATE TOP 10 SET user_number_1 = 123 ORDER BY ground_level DESC

It is, of course, possible to use a where clause e.g.

UPDATE TOP 10 SET user_number_1 = 123 WHERE ground_level < 100 ORDER BY ground_level DESC

It is possible to use the 'rank' of an object, that is to say the position it appears in the list e.g.

SET user_number_1 = rank ORDER BY ground_level DESC

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.

SELECT node_id,rank ORDER BY ground_level DESC

is not permitted. If you wish to do something like this it is possible to set the rank into a variable and use that in a subsequent clause in the query. e.g.

SET $r = rank ORDER BY ground_level DESC; SELECT node_id,ground_level,$r ORDER BY $r ASC

If there are tied values in the sort order i.e. two objects have the same values in all the sort fields then all objects with the same value will be given equal rank e.g.

SET $r = rank ORDER BY INT(ground_level) DESC; SELECT oid,$r,INT(ground_level) DP 0 ORDER BY ground_level DESC

yields