SQL Example: Using Selection Query Functions

In this example we manipulate selections using a query.


Important: For simplicity, the following examples do not include a THREADS SystemCpuCount() command in the query.  When writing queries manually using the Command Window we should make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in our system.  See the THREADS command for more info.


We have opened the mfd_meta table in two windows.  The easiest way to do that is to first open the table in a window and to then choose the Window - New Window command to open a second window for that same table.




In the first window we have selected two records, the second and the fourth.




In the second window we have selected the first record.   Note that this shows how we can open the same table simultaneously in two different windows and how each of those windows can have a different selection.


The easiest interactive way to see the selection set number for a given window is to open the window and see what the Info pane reports as the selection set number for that window.    For a fully SQL approach to find the number of the selection set for each window we can use the SelectionForWindow query function. In the Command Window command pane we enter and run:


VALUES (SelectionForWindow('mfd_meta', ''));


The above gives us a result table with a single result record with a value of 0, the selection set number for the mfd_meta window.  Another way to get that value is to use the expression evaluation operator ? to run:


? SelectionForWindow('mfd_meta', '')


Which evaluates the expression to report a value of 0 as well, reported with float64 precision, no less. Manifold cuts no corners when doing math.


To get the selection set number for the other window, mfd_meta (2) we can run


VALUES (SelectionForWindow('mfd_meta (2)', ''));


...to get a result table with a single result record with a value of 1.


Now that we know the selection set number for each window we can use query functions to write queries that get the selection of any given window.   For example, using the selection set number, 0, for the mfd_meta window we can run:


EXECUTE CALL Selection([mfd_meta], 0, TRUE)


...to get a results table of:




That shows the two records that are selected in that window..   If we wanted a results table of the records not selected we could run:


EXECUTE CALL Selection([mfd_meta], 0, FALSE)


...to get a results table of:




Using the selection set number of the mfd_meta (2) window, 1, we can run:


EXECUTE CALL Selection([mfd_meta], 1, FALSE)


...to get a results table of:




...listing records not selected in the mfd_meta (2) window.    Note that if we did not want to use two steps, a query function to find the selection set number for a window and then a second step to use that number we could have written both steps in a single line with SelectionForWindow evaluated as an argument to Selection:


EXECUTE CALL Selection([mfd_meta], SelectionForWindow('mfd_meta', ''), TRUE)


To see if a selection set is empty, for example, the selection set number 0 for the mfd_meta window, we can run:


? SelectionIsEmpty(0)


The result is 0, that is, FALSE, which is the right answer because the selection is not empty in that window.


To see if that selection set is inverted, we run:


? SelectionIsInverted(0)


The result also is 0, that is FALSE, which again is the right answer because the selection in that window is not inverted.   Knowing that tells us how to interpret the result of running the SelectionKeys query function for that window:


EXECUTE CALL SelectionKeys([mfd_meta],0)


Note that we are back to using EXECUTE CALL to launch the function since it returns a table, which in this case will be:




The above reports the selection key values, for the mfd_id field, for the 0 selection set in the mfd_meta table.  We know because the table is not inverted that those keys are for selected records.  Note that the reported keys are accurate because indeed in the mfd_meta table we have selected those two records with mfd_id values of 2 and 4.


Consider what happens if we click on the mfd_meta table window and choose Edit - Select Inverse:




We invert the selection in the window so that the second and fourth records are deselected while the first and the third records are now selected.    Suppose we now repeat the SelectionKeys query:


EXECUTE CALL SelectionKeys([mfd_meta],0)


The result once again will be:




...the same as before, showing the same keys involved in the selection.  That might at first be puzzling, until we see if the selection set is inverted or not by running:


? SelectionIsInverted(0)


...which now returns a value of 1 meaning TRUE, the selection is inverted so that the keys are pointing at records which are not selected.


The above example shows the efficiency of Manifold's use of polarity to interpret the meaning of selection sets.   Suppose we had a table with a million records in it and we wanted to select all of them except one or two erroneous records.   A quick way to do that is to select the one or two erroneous records and then choose Edit - Select Inverse.   Manifold can do that instantaneously because there are only one or two records in the selection set, not a million, so the system just marks the selection set as inverted and thereafter Manifold knows the selection set lists records which are not selected.  That's a lot quicker than building a new list of the million records which are selected.


See Also



Select Dialog




Example: Using a Selection Box in a Table - In tables we can use the mouse to select records by drawing a selection box.  Ctrl-click and drag anywhere in the table to select all records with any part in the selection box.   Using the Alt key makes that a deselect: Alt-Ctrl click and drag anywhere in the table to deselect records with any part of the record in the deselection box.


Example: Mouse Selection in a Drawing Window - Using the mouse to select objects in a drawing window.   This could be a drawing layer in a map or simply in a drawing window.


Example: Combining Selections using the Select Dialog - How to use selections to solve a one-off problem casually that would be more difficult to solve rigorously with SQL.   The objective in the example's scenario is to visit a point of tourist interest while travelling.    We will use mouse selection as well as the Select dialog.


Example: Construct JSON String using Select and Transform - Use the Select and Transform dialogs to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.