Special Columns

An introduction to the use of special columns in data.world custom SQL.

In the section on multi-tables we introduced the special column table_name which lists the name of the source table for each row returned from a multi-table query. There are two more data.world special columns called row_index and row_address. These columns exist for every table in a data.world dataset. The first, row_index, just refers to the order of the row in the table, starting with 0. The row_address column contains a unique URL generated for the row. Under the covers, SQL at data.world is implemented using semantic web technology. One of the key ideas of the semantic web is that every entity should have its own unique URL.

Unlike normal columns, row_index and row_address will not be returned by default using SELECT * in a query. Instead they must be explicitly listed. To query all of the data for the products table in our dataset and it’s associated special columns, we would write a query like this:

SELECT products.*,
  FROM products

The resulting table would look like this:

product sales_price row_index row_address
GTX Basic 550 0 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-0
GTX Pro 4821 1 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-1
MG Special 55 2 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-2
MG Advanced 3393 3 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-3
GTX Plus Pro 5482 4 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-4
GTX Plus Basic 1096 5 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-5
GTK 500 26768 6 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-6
MG Mono 17 7 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-7
Alpha Caryad 245 8 https://siyeh.linked.data.world/d/sql-crm-example-data/row-products-8