# SQL Select

> The SQL `SELECT` statement is used to display the contents of the specified columns in a table (known as “projection”). eXtremeSQL supports almost all standard SQL constructions. (Please see the wikipedia page for a detailed description of the select statement syntax.)

> Some important eXtremeSQL features and SQL extensions are described in the sections below.

> ## Order By

> The `order by` clause causes the result set to be sorted by the specified columns. For example, consider the following table and index definition initialized with 6 initial rows:

>         create table orders(id integer not null, tm integer not null);
>         create index idx on orders(id,tm);
>         insert into orders values (2,  200);
>         insert into orders values (2,  220);
>         insert into orders values (2,  210);
>         insert into orders values (1,  100);
>         insert into orders values (1,  120);
>         insert into orders values (1,  110);

> A simple select displays the rows in the order of insertion:

>         select * from orders;
>         id      tm
>         ---------------------
>         2       200
>         2       220
>         2       210
>         1       100
>         1       120
>         1       110

> Now, using the `order by` clause, we can sort the result set in ascending order by column `id` and `tm` as follows:

>         select * from orders order by id, tm;
>         id      tm
>         -------------------------------------
>         1       100
>         1       110
>         1       120
>         2       200
>         2       210
>         2       220

> Or sort the result set rows in descending order as follows:

>         select * from orders order by id desc, tm desc;
>         id      tm
>         -----------------------------------------------
>         2       220
>         2       210
>         2       200
>         1       120
>         1       110
>         1       100

> ## Distinct Order

> A useful eXtremeSQL extension is the possibility to add the `distinct` keyword in the `order by` clause. This allows restricting the result set to a single record from an ordered subset. For example, using the `distinct order` feature, we can extract the first row from each "ordered subset" defined by the values of column `id` as follows:

>         select * from orders order by id distinct,  tm;
>         id    tm
>         ----------------------------------------------
>         1    100
>         2    200

> Or we can extract the last row from the "ordered subset" by specifying the `descending` order as follows:

>         select * from orders order by id desc distinct, tm desc;
>         id    tm
>         --------------------------------------------------------
>         2    220
>         1    120

> ## For Update

> The `for update` clause in a SQL select statement is used to indicate that the result set (cursor) is going to be used subsequently for updating data values. Note that the eXtremeSQL implementation of the ``for update`` clause differs slightly from the standard. Whereas the standard specifies “For Update On” where the “On” preposition requires specification of a list of columns that will be subject to updates, eXtremeSQL does not require the “On list” – all columns are modifiable. In eXtremeSQL, the `for update` clause instructs the runtime to avoid issuing a transaction upgrade (from `READ_ONLY` to `READ_WRITE`), but rather to open a `READ_WRITE` transaction from the outset.

> ## The Ignore Column Operator for Sequences

> The exclamation point (`"!"`) can be used as a shortcut for the sequence function `seq_ignore()` to indicate that a calculated column is to be ignored in the result set output. For example consider the following table with one ordered and two unordered sequence columns:

>         create table Quotes(sym string primary key,
>             day sequence(int asc),
>             open sequence(int),
>             close sequence(int));
>         insert into Quotes values('AAA',
>             [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
>             [101, 102, 103, 104, 105, 109, 110],
>             [111, 112, 113, 114, 115, 119, 120]);
>         insert into Quotes values('BBB',
>             [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
>             [201, 202, 203, 204, 205, 209, 210],
>             [211, 212, 213, 214, 215, 219, 220]);
>         insert into Quotes values('CCC',
>             [20170501, 20170502, 20170503, 20170504, 20170505, 20170509, 20170510],
>             [301, 302, 303, 304, 305, 309, 310],
>             [311, 312, 313, 314, 315, 319, 320]);

>         select !seq_search(day, 20170501, 20170503) as daterange,
>             open@daterange as "open", close@daterange as "close"
>         from Quotes;

>         daterange open    close
>         ------------------------------------------------------------------------------
>         {?}     {101, 102, 103} {111, 112, 113}
>         {?}     {201, 202, 203} {211, 212, 213}
>         {?}     {301, 302, 303} {311, 312, 313}

>         Selected records: 3

> In this query we select an interval with function `seq_search()` and, as we don't need all timestamps in the interval in the query result, we mark the column as ignored with the`!` operator. Ignored column may be used in other calculated columns but will not appear in the actual statement output.

> ## SELECT ... AS

> The `AS` command is used to rename a column with a column alias.

>         XSQL>create table T (t text, i int);
>         XSQL>insert into T values ('john', 1);
>         XSQL>insert into T values ('mike', 2);
>         XSQL>select t as name from T;
>         name
>         ------------------------------------------------------------------------------
>         john
>         mike

>         Selected records: 2

> A column alias only exists for the duration of the query. Standard SQL does not permit references to column aliases in `WHERE` clauses because the `WHERE` clause is evaluated before the `SELECT` clause: `SELECT` is always the last step in the execution path. As a result, the alias is not yet defined when `WHERE` is processed.

>         XSQL>select t as name from T where name='john';
>         ERROR: Compiler error in line 1 at position 22: Failed to locate column 'name'
>         name from T where name='john'
>                           ^

> An "inline view" can be used as a workaround - a subquery with an alias that can be treated like a table. This effectively allows the `SELECT` to be evaluated first, as shown in the example below.

>         XSQL>select * from (select t as name from T) where name='john';
>         name
>         ------------------------------------------------------------------------------
>         john

>         Selected records: 1