Select statement - MapBasic - 2023

Purpose

Selects particular rows and columns from one or more open tables, and treats the results as a separate, temporary table. Also provides the ability to sort and sub-total data. You can issue this statement from the MapBasic window in MapInfo Pro.

Syntax

Select expression_list
	From table_name [ , ... ] [ Where expression_group ]
		[ Into results_table [ Noselect ] [ Hide ] ] 
		[ Group By column_list ] 
		[ Order By column_list ] [Limit n]

expression_list is a comma-separated list of expressions which will comprise the columns of the Selection results.

expression_group is a list of one or more expressions, separated by the keywords AND or OR.

table_name is the name of an open table.

results_table is the name of the table where query results should be stored.

column_list is a list of one or more names of columns, separated by commas.

n is the number of records returned by a Select statement.

Description

The Select statement provides MapBasic programmers with the capabilities of MapInfo Pro's SQL Select dialog box.

The MapBasic Select statement is modeled after the Select statement in the Structured Query Language (SQL). Thus, if you have used SQL-oriented database software, you may already be familiar with the Select statement. Note, however, that MapBasic's Select statement includes geographic capabilities that you will not find in other packages.

Column expressions (for example, tablename.columnname) in a Select statement may only refer to tables that are listed in the Select statement's From clause. For example, a Select statement may only incorporate the column expression STATES.OBJ if the table STATES is included in the statement's From clause.

The Select statement serves a variety of different purposes. One Select statement might apply a test to a table, making it easy to browse only the records which met the criteria (this is sometimes referred to as filtering). Alternately, Select might be used to calculate totals or subtotals for an entire table. Select can also: sort the rows of a table; derive new column values from one or more existing columns; or combine columns from two or more tables into a single results table.

Generally speaking, a Select statement queries one or more open tables, and selects some or all of the rows from said table(s). The Select statement then treats the group of selected rows as a results table; Selection is the default name of this table (although the results table can be assigned another name through the Into clause). Following a Select statement, a MapBasic program―or, for that matter, a MapInfo Pro user-can treat the results table as any other MapInfo table.

You can also use the Select statement to update the WHERE clause conditions of a query and/or refresh the query without recreating a new query table. See Updating a Select Statement for more details.

After issuing a Select statement, a MapBasic program can use the SelectionInfo() function to examine the current selection.

The Select statement format includes several clauses, most of which are optional. The nature and function of a Select statement depend upon which clauses are included. For example: if you wish to use a Select statement to set up a filter, you should include a Where clause; if you wish to use a Select statement to subtotal the values in the table, you should include a Group By clause; if you want MapBasic to sort the results of the Select statement, you should include an Order By clause. Note that these clauses are not mutually exclusive; one Select statement may include all of the optional clauses.

Select clause

This clause dictates which columns MapBasic should include in the results table. The simplest type of expression_list is an asterisk character ("*"). The asterisk signifies that all columns should be included in the results. The statement:

Select * From world

tells MapBasic to include all of the columns from the "world" table in the results table. Alternately, the expression_list clause can consist of a list of expressions, separated by commas, each of which represents one column to include in the results table. Typically, each of these expressions involves the names of one or more columns from the table in question. Very often, MapBasic function calls and/or operators are used to derive some new value from one or more of the column names.

For example, the following Select statement specifies an expression_list clause with two expressions:

Select country, Round(population,1000000) 
	From world

The expression_list above consists of two expressions, the first of which is a simple column name (country), and the second of which is a function call (Round()) which operates on another column (population).

After MapBasic carries out the above Select statement, the first column in the results table will contain values from the world table's name column. The second column in the results table will contain values from the world table's population column, rounded off to the nearest million.

Each expression in the expression_list clause can be explicitly named by having an alias follow the expression; this alias would appear, for example, at the top of a Browser window displaying the appropriate table. The following statement would assign the field alias "Millions" to the second column of the results table:

Select country,Round(population,1000000) "Millions" From world

Alternatively, you can also use the AS keyword to assign an alias name to a column.

Select country,Round(population,1000000) AS "Millions" From world

Any mappable table also has a special column, called object (or obj for short). If you include the column expression obj in the expression_list, the resultant table will include a column which indicates what type of object (if any) is attached to that row.

The expression_list may include either an asterisk or a list of column expressions, but not both. If an asterisk appears following the keyword Select, then that asterisk must be the only thing in the expression_list. In other words, the following statement would not be legitimate:

Select *, object From world ' this won't work!

Select usa_caps.Capital, a.* , usa_caps.Capital from states as "a" , usa_caps where states.state =
      usa_caps.state

From clause

The From clause specifies which table(s) to select data from. If you are doing a multiple-table join, the tables you are selecting from must be base tables, rather than the results of a previous query.

Each table can be explicitly named by having an alias follow the table name using the AS keyword. The following statement would assign the table alias "b" to the usa_caps table and "a" to the states table.

Select a.State, b.Capital from usa_caps AS "b", states AS "a" where a.state = b.state

Where clause

One function of the Where clause is to specify which rows to select. Any expression can be used (see Expressions section below). Note, however, that groups of two or more expressions must be connected by the keywords And or Or, rather than being comma-separated. For example, a two-expression Where clause might read like this:

Where Income > 15000 And Income < 25000

Note that the And operator makes the clause more restrictive (both conditions must evaluate as TRUE for MapBasic to select a record), whereas the Or operator makes the clause less restrictive (MapBasic will select a record if either of the expressions evaluates to TRUE).

By referring to the special column name object, a Where clause can test geographic aspects of each row in a mappable table. Conversely, the expression "Not object" can be used to single out records which do not have graphical objects attached.

For example, the following Where clause would tell MapBasic to select only those records which are currently un-geocoded:

Where Not Object

If a Select statement is to use two or more tables, the statement must include a Where clause, and the Where clause must include an expression which tells MapBasic how to join the two tables. Such a join-related expression typically takes the form Where tablename1.field = tablename2.field, where the two fields have corresponding values. The following example shows how you might join the tables "States" and "City_1k." The column City_1k.state contains two-letter state abbreviations which match the abbreviations in the column States.state.

Where States.state = City_1k.state

Alternately, you can specify a geographic operator to tell MapInfo Pro how to join the two tables.

Where states.obj Contains City_1k.obj 

A Where clause can incorporate a subset of specific values by including the Any or All keyword. The Any keyword defines a subset, for the sake of allowing the Where clause to test if a given expression is TRUE for any of the values in the subset. Conversely, the All keyword defines a subset, for the sake of allowing the Where clause to test if a given condition is true for all of the values in the subset.

The following query selects any customer record whose state column contains "NY," "MA," or "PA." The Any() function functions the same way as the SQL "IN" operator.

Select * From customers
	Where state = Any ("NY", "MA", "PA")

A Where clause can also include its own Select statement, to produce what is known as a subquery. In the next example, we use two tables: "products" is a table of the various products which our company sells, and "orders" is a table of the orders we have for our products. At any given time, some of the products may be sold out. The task here is to figure out which orders we can fill, based on which products are currently in stock. This query uses the logic, "select all orders which are not among the list of items that are currently sold out."

Select * From orders 
	Where partnum <> 
	All(Select partnum from products 
		where not instock)

On the second line of the query, the keyword Select appears a second time; this produces our sub-select. The sub-select builds a list of the parts that are currently not in stock. The Where clause of the main query then uses All() function to access the list of unavailable parts.

In the example above, the sub-select produces a set of values, and the main Select statement's Where clause tests for inclusion in that set of values. Alternately, a sub-select might use an aggregate operator to produce a single result.

The example below uses the Avg() aggregate operator to calculate the average value of the pop field within the table states.

Accordingly, the net result of the following Select statement is that all records having higher-than-average population are selected.

Select * From states
	Where population > 
		(Select Avg(population) From states)

MapInfo Pro also supports the SQL keyword In. A Select statement can use the keyword In in place of the operator sequence = Any. In other words, the following Where clause, which uses the Any keyword:

Where state = Any ("NY", "MA", "PA")

is equivalent to the following Where clause, which uses the In keyword:

Where state In ("NY", "MA", "PA")

In a similar fashion, the keywords Not In may be used in place of the operator sequence: <> All.

' Note: the following statement, which illustrates
' correlated subqueries, will NOT work in MapBasic

Select * from leads
Where lead.name = 
	(Select var.name From vars 
		Where lead.name = customer.name)

This limitation is primarily of interest to users who are already proficient in SQL queries, through the use of other SQL-compatible database packages.

Into clause

This optional clause lets you name the results table. If no Into clause is specified, the resulting table is named Selection. Note that when a subsequent operation references the Selection table, MapInfo Pro will take a "snapshot" of the Selection table, and call the snapshot QUERYn (for example, QUERY1).

If you include the Noselect keyword, the statement performs a query without changing the pre-existing Selection table. Use the NoSelect keyword if you need to perform a query, but you do not want to de-select whatever rows are already selected.

If you include the Noselect keyword, the query does not trigger the SelChangedHandler procedure.

If you include the Hide keyword, the query table is created as a hidden table. This is useful while creating a query that is used for programmatic processing, but does not need to be presented to the user using the the user interface, for example, the Table List. Use of the Hide keyword, therefore, can sometimes significantly reduce unnecessary overhead, if the program issues multiple Select … Into… statements.

Group By clause

This optional clause specifies how to group the rows when performing aggregate functions (sub-totalling). In a Group By clause, you typically specify a column name (or a list of column names); MapBasic then builds a results table containing subtotals. For example, if you want to subtotal your table on a state-by-state basis, your Group By clause should specify the name of a column which contains state names. The Group By clause may not reference a function with a variable return type, such as the ObjectInfo() function.

The aggregate functions Sum(), Min(), Max(), Count(*), Avg(), and WtAvg() allow you to calculate aggregated results.

Suppose the Q4Sales table describes sales information for the fourth fiscal quarter. Each record in this table contains information about the dollar amount of a particular sale. Each record's Territory column indicates the name of the territory where the sale occurred. The following query counts how many sales occurred within each territory, and calculates the sum total of all of the sales within each territory.

Select territory, Count(*), Sum(amount)
	From q4sales
	Group By territory

The Group By clause tells MapBasic to group the table results according to the contents of the Territory column, and then create a subtotal for each unique territory name. The expression list following the keyword Select specifies that the results table should have three columns: the first column will state the name of a territory; the second column will state the number of records in the q4sales table "belonging to" that territory; and the third column of the results table will contain the sum of the Amount columns of all records belonging to that territory.

The following table describes MapInfo Pro's aggregate functions.

The following table describes MapInfo Pro's spatial aggregate functions.

Select Aggregatecombine(obj) from usa_caps, states where usa_caps.state = states.state //combine of all objects from usa_caps table.

Select Aggregatebuffer(obj, 20, 10, "mi") from usa_caps //Buffer and Combine of all the objects from usa_caps table.

Select AggregateCombine(world.obj) from World group by World.Continent Into Continents

Use the Wtavg() aggregate function to calculate weighted averages. For example, the following statement uses the Wtavg() function to calculate a weighted average of the literacy rate in each continent:

Select continent, Sum(pop_1994), WtAvg(literacy, Pop_1994)
	From World 
	Group By continent 
	Into Lit_query 

Because of the Group By clause, MapInfo Pro groups rows of the table together, according to the values in the Continent column. All rows having "North America" in the Continent column will be treated as one group; all rows having "Asia" in the Continent column will be treated as another group; etc. For each group of rows-in other words, for each continent―MapInfo Pro calculates a weighted average of the literacy rates.

A simple average (using the Avg() function) calculates the sum divided by the count. A weighted average (using the WtAvg() function) is more complicated, in that some rows affect the average more than other rows. In this example, the average calculation is weighted by the Pop_1994 (population) column; in other words, countries that have a large population will have more of an impact on the result than countries that have a small population.

In the preceding example, the Group By territory clause identifies the Territory column by name. Alternately, a Group By clause can identify a column by a number, using an expression of the form col#. In this type of expression, the # sign represents an integer number, having a value of one or more, which identifies one of the columns in the Select clause. Thus, the above Select statement could have read Group By col1, or even Group By 1, rather than Group By territory.

It is sometimes necessary to use one of these alternate syntaxes. If you wish to Group By a derived expression, which does not have a column name, then the Group By clause must use the col# syntax or the # syntax to refer to the proper column expression. In the following example, we Group By a column value derived through the Month() function. Since this column expression does not have a conventional column name, our Group By clause refers to it using the col# format:

Select Month(sick_date), Count(*)
	From sickdays
	Group By 1 

This example assumes that each row in the sickdays table represents a sick day claim. The results from this query would include twelve rows (one row for each month); the second column would indicate how many sick days were claimed for that month.

Depending on your application, you may need to specify more than one column in the Group By clause; this happens when the contents of a column are not sufficiently unique. For example, you may have a table describing counties across the United States. County names are not unique; for example, many different states have a Franklin county. Therefore, if your Group By clause specifies a single county-name column, MapBasic will create one sub-total row in the results table for the county "Franklin". That row would summarize all counties having the name "Franklin", regardless of whether the records were in different states.

When this type of problem occurs, your Group By clause must specify two or more columns, separated by commas. For example, a group by clause might read:

	Group By county, state

With this arrangement, MapBasic would construct a separate group of rows (and, thus, a separate sub-total) for each unique expression of the form countyname, statename. The results table would have separate rows for Franklin County, MA versus Franklin County, FL.

Order By clause

This optional clause specifies which column or set of columns to order the results by. As with the Group By clause, the column is specified by name in the field list, or by a number representing the position in the field list. Multiple columns are separated by commas.

By default, results sorted by an Order By clause are in ascending order. An ascending character sort places "A" values before "Z" values; an ascending numeric sort places small numbers before large ones. If you want one of the columns to be sorted in descending order, you should follow that column name with the keyword DESC.

	Select * From cities 
		Order By state, population Desc

This query performs a two-level sort on the table Cities. First, MapBasic sorts the table, in ascending order, according to the contents of the state column. Then MapBasic sorts each state's group of records, using a descending order sort of the values in the population column. Note that there is a space, not a comma, between the column name and the keyword DESC.

The Order By clause may not reference a function with a variable return type, such as the ObjectInfo() function.

Limit clause

The Limit clause allows you to specify the number of records returned by a Select statement. You can use it to sample the results or to preview the query, without having to wait for a query to finish. It is also useful for selecting the n highest, or n lowest data from a table. The value n must be greater than 0. If n is negative, an error is displayed. The MapBasic error code for this error is 1055.

Select * From Countries Where Pop_1994 > 2000000 And Pop_1994 < 3000000 Limit 5

The above query stops processing as soon as the first five results of the WHERE clause are found.

Select * From Countries Order By Pop_1994 Desc Limit 5

The above result set shows the top five countries with the largest "1994" populations ordered from largest to smallest. Because of the Order By clause, the query would still process every record before we can know what the top five results are. This means you may still have to wait a long time with large tables before results can be limited.

Select * From Countries Where Pop_1994 > 2000000 And Pop_1994 < 3000000 Order By Pop_1994 Desc
      Limit 5

The above query first finds all records using the WHERE clause, then order the results by "Pop_1994" largest to smallest, then returns the top five results.

Geographic Operators

MapBasic supports several geographic operators: Contains, Contains Part, Contains Entire, Within, Partly Within, Entirely Within, and Intersects. These operators can be used in any expression, and are very useful within the Select statement's Where clause. All geographic operators are infix operators (operate on two objects and return a boolean). The operators are listed in the table below.

Selection Performance

Some Select statements are considerably faster than others, depending in part on the contents of the Where clause.

If the Where clause contains one expression of the form:

columnname = constant_expression 

or if the Where clause contains two or more expressions of that form, joined by the And operator, then the Select statement will be able to take maximum advantage of indexing, allowing the operation to proceed quickly. However, if multiple Where clause expressions are joined by the Or operator instead of by the And operator, the statement will take more time, because MapInfo Pro will not be able to take maximum advantage of indexing.

Similarly, MapInfo Pro provides optimized performance for Where clause expressions of the form:

[ tablename. ] obj geographic_operator object_expression 

and for Where clause expressions of the form:

RowID = constant_expression 

RowID is a special column name. Each row's RowID value represents the corresponding row number within the appropriate table; in other words, the first row in a table has a RowID value of one.

Cross Joins

For example, to find objects near each other:

Select A.ID, B.NAME From SomeTable1 as "A", SomeTable2 as "B" Where ObjectDistance(A.OBJ, B.OBJ, "m") < 100

Specifying the Object Table in a Join

Select usa_caps.state, states.obj object from usa_caps,states where usa_Caps.state = states.state //In case of join, the object used to come from the first table by default, but in this query we
      have specified the object keyword to states.obj therefore now the query will have object from the
      states table.

Open Table "D:\Maps\us\usa_caps.TAB" Interactive
Open Table "D:\Maps\Imp_data\STATES.TAB" Interactive
select usa_caps.state, states.obj as "StateObj", Buffer(OBJ, 20, 10, "mi") "BufferObj" Object from usa_caps, states where usa_Caps.state = states.state into query
Map From query
set map window frontwindow() layer 0 Editable On
Set Map Window frontwindow() Layer 1 Display Off
Dim aOBJ as Object
fetch first from query
aOBJ = query.StateObj ---> Here you will get cannot convert data error because we have a object expression in query.
Insert Into WindowInfo(frontwindow(), 10) (OBJ) Values(aOBJ)


select usa_caps.state, states.obj as "StateObj" object, Buffer(OBJ, 20, 10, "mi") "BufferObj" from usa_caps, states where usa_Caps.state = states.state into query
Map From query
set map window frontwindow() layer 0 Editable On
Set Map Window frontwindow() Layer 1 Display Off
Dim aOBJ as Object
fetch first from query
aOBJ = query.BufferObj --> This will work, because we don't have object expression in query. We just have specified which table the object should come from.
Insert Into WindowInfo(frontwindow(), 10) (OBJ) Values(aOBJ)

Examples

This example selects all customers that are in New York, Connecticut, or Massachusetts. Each customer record does not need to include a state name; rather, the query relies on the geographic position of each customer object to determine whether that customer is "in" a given state.

Select * From customers
	Where obj Within Any(Select obj From states 
		Where state = "NY" or state = "CT" or state = "MA")

The next example demonstrates a sub-select. Here, we want to select all sales territories which contain customers that have been designated as "Federal." The subselect selects all customer records flagged as Federal, and then the main select works from the list of Federal customers to select certain territories.

Select * From territories
	Where obj Contains Any (Select obj From customers 
		Where customers.source = "Federal")

The following query selects all parcels that touch parcel 120059.

Select * From parcels
	Where obj Intersects (Select obj From parcels
		Where parcel_id = 120059)

Updating a Select statement

You can update the where clause condition of a query and/or refresh it without recreating a new query table.

Syntax:

Select Update For table_name [Cross Join] [Where expression] [Refresh [All]]

table_name: is the name of an existing query table. Query table should not be having any group by clause, aggregators (spatial, non-spatial), or derived objects and should have a where clause.

expression: is a list of one or more expressions, separated by the keywords AND or OR.

Refresh : Optional Token to refresh the query table map, browser, and theme. Specifying only the Refresh token will not recalculate the theme bins. To recalculate the theme bins, specify the Refresh All token. The Refresh token if used without a where clause will re-evaluate the queries' existing where condition, without closing the table and its dependent windows. This is useful in case the table data changes and the query needs to be re-evaluated.

Open Table "D:\Maps\Imp_data\STATES.TAB" Interactive
Select * from States Where State_Name Like "%al%" into testtable noselect
browse * from testtable
map from testtable   
Select Update For testtable Where State_Name Like "al%"

Open Table "D:\Maps\Imp_data\STATES.TAB" Interactive
Select * from States Where State_Name Like "%al%" into testtable noselect
browse * from testtable
map from testtable  
Select Update testtable refresh
//Will re-evaluate the query table where condition in case underlying data changes, 
without closing the query table.

See Also: