title_wip

Extreme values are calculated for rows before `LIMIT`, but after `LIMIT BY`. However, when using `LIMIT offset, size`, the rows before `offset` are included in `extremes`. In stream requests, the result may also include a small number of rows that passed through `LIMIT`.

### Notes[​](#notes "Direct link to Notes")

You can use synonyms (`AS` aliases) in any part of a query.

The `GROUP BY`, `ORDER BY`, and `LIMIT BY` clauses can support positional arguments. To enable this, switch on the [enable\_positional\_arguments](/docs/operations/settings/settings#enable_positional_arguments) setting. Then, for example, `ORDER BY 1,2` will be sorting rows in the table on the first and then the second column.

## Implementation Details[​](#implementation-details "Direct link to Implementation Details")

If the query omits the `DISTINCT`, `GROUP BY` and `ORDER BY` clauses and the `IN` and `JOIN` subqueries, the query will be completely stream processed, using O(1) amount of RAM. Otherwise, the query might consume a lot of RAM if the appropriate restrictions are not specified:

- `max_memory_usage`
- `max_rows_to_group_by`
- `max_rows_to_sort`
- `max_rows_in_distinct`
- `max_bytes_in_distinct`
- `max_rows_in_set`
- `max_bytes_in_set`
- `max_rows_in_join`
- `max_bytes_in_join`
- `max_bytes_before_external_sort`
- `max_bytes_ratio_before_external_sort`
- `max_bytes_before_external_group_by`
- `max_bytes_ratio_before_external_group_by`

For more information, see the section "Settings". It is possible to use external sorting (saving temporary tables to a disk) and external aggregation.

## SELECT modifiers[​](#select-modifiers "Direct link to SELECT modifiers")

You can use the following modifiers in `SELECT` queries.

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Modifier Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [`APPLY`](/docs/sql-reference/statements/select/apply-modifier) Allows you to invoke some function for each row returned by an outer table expression of a query.|  |  |  |  | | --- | --- | --- | --- | | [`EXCEPT`](/docs/sql-reference/statements/select/except-modifier) Specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.|  |  | | --- | --- | | [`REPLACE`](/docs/sql-reference/statements/select/replace-modifier) Specifies one or more [expression aliases](/docs/sql-reference/syntax#expression-aliases). Each alias must match a column name from the `SELECT *` statement. In the output column list, the column that matches the alias is replaced by the expression in that `REPLACE`. This modifier does not change the names or order of columns. However, it can change the value and the value type. | | | | | | | |

### Modifier Combinations[​](#modifier-combinations "Direct link to Modifier Combinations")

You can use each modifier separately or combine them.

**Examples:**

Using the same modifier multiple times.

```
SELECT COLUMNS('[jk]') APPLY(toString) APPLY(length) APPLY(max) FROM columns_transformers;
```

```
┌─max(length(toString(j)))─┬─max(length(toString(k)))─┐
│                        2 │                        3 │
└──────────────────────────┴──────────────────────────┘
```

Using multiple modifiers in a single query.

```
SELECT * REPLACE(i + 1 AS i) EXCEPT (j) APPLY(sum) from columns_transformers;
```

```
┌─sum(plus(i, 1))─┬─sum(k)─┐
│             222 │    347 │
└─────────────────┴────────┘
```

## SETTINGS in SELECT Query[​](#settings-in-select-query "Direct link to SETTINGS in SELECT Query")

You can specify the necessary settings right in the `SELECT` query. The setting value is applied only to this query and is reset to default or previous value after the query is executed.

Other ways to make settings see [here](/docs/operations/settings/overview).

For boolean settings set to true, you can use a shorthand syntax by omitting the value assignment. When only the setting name is specified, it is automatically set to `1` (true).

**Example**

```
SELECT * FROM some_table SETTINGS optimize_read_in_order=1, cast_keep_nullable=1;
```

- [Syntax](#syntax)- [SELECT Clause](#select-clause)
    - [Dynamic column selection](#dynamic-column-selection)- [Asterisk](#asterisk)- [Extreme Values](#extreme-values)- [Notes](#notes)- [Implementation Details](#implementation-details)- [SELECT modifiers](#select-modifiers)
        - [Modifier Combinations](#modifier-combinations)- [SETTINGS in SELECT Query](#settings-in-select-query)

Was this page helpful?