# ColumnStore SELECT

The `SELECT` statement is used to query the database and display table data. You can add many clauses to filter the data.

## Syntax

```sql
SELECT
[ALL | DISTINCT ]
    select_expr [, select_expr ...]
    [ FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | POSITION} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | POSITION} [ASC | DESC], ...]
      [LIMIT {[offset,] ROW_COUNT | ROW_COUNT OFFSET OFFSET}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options]
         | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ]
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
```

## Projection List (SELECT)

If the same column needs to be referenced more than once in the projection list, a unique name is required for each column using a column alias. The total length of the name of a column, inclusive of the length of functions, in the projection list must be 64 characters or less.

## WHERE

The `WHERE` clause filters data retrieval based on criteria. Note that *`column_alias`* cannot be used in the `WHERE` clause. The following statement returns rows in the region table where the region = ‘ASIA’:

```sql
SELECT * FROM region WHERE name = ’ASIA’;
```

## GROUP BY

`GROUP BY` groups data based on values in one or more specific columns. The following statement returns rows from the *lineitem* table where /orderkey\_is less than 1 000 000 and groups them by the quantity.\_

```sql
SELECT quantity, COUNT(*) FROM lineitem WHERE orderkey < 1000000 GROUP BY quantity;
```

## HAVING

`HAVING` is used in combination with the `GROUP BY` clause. It can be used in a `SELECT` statement to filter the records that a `GROUP BY` returns. The following statement returns shipping dates, and the respective quantity where the quantity is 2500 or more.

```sql
SELECT shipdate, COUNT(*) FROM lineitem GROUP BYshipdate HAVING COUNT(*) >= 2500;
```

## ORDER BY

The `ORDER BY` clause presents results in a specific order. Note that the `ORDER BY` clause represents a statement that is post-processed by MariaDB. The following statement returns an ordered *quantity* column from the *lineitem* table.

```sql
SELECT quantity FROM lineitem WHERE orderkey < 1000000 ORDER BY quantity;
```

The following statement returns an ordered *shipmode* column from the *lineitem* table.

```sql
SELECT shipmode FROM lineitem WHERE orderkey < 1000000 ORDER BY 1;
```

**NOTE:** When `ORDER BY` is used in an inner query and `LIMIT` on an outer query, `LIMIT` is applied first and then `ORDER BY` is applied when returning results.

## UNION

Used to combine the result from multiple `SELECT` statements into a single result set. The `UNION` or `UNION DISTINCT` clause returns query results from multiple queries into one display and discards duplicate results. The `UNION ALL` clause displays query results from multiple queries and does not discard the duplicates. The following statement returns the *`p_name`* rows in the *part* table and the *partno* table and discards the duplicate results:

```sql
SELECT p_name FROM part UNION SELECT p_name FROM  partno;
```

The following statement returns all the *`p_name` rows* in the *part* table and the *partno* table:

```sql
SELECT p_name FROM part UNION ALL SELECT p_name FROM  partno;
```

## LIMIT

A limit is used to constrain the number of rows returned by the `SELECT` statement. `LIMIT` can have up to two arguments. `LIMIT` must contain a row count and may optionally contain an offset of the first row to return (the initial row is 0).

The following statement returns 5 customer keys from the customer table:

```sql
SELECT custkey FROM customer LIMIT 5;
```

The following statement returns 5 customer keys from the customer table beginning at offset 1000:

```sql
SELECT custkey FROM customer LIMIT 1000,5;
```

{% hint style="info" %}
When `LIMIT` is used in a nested query, and the inner query contains an `ORDER BY` clause, `LIMIT` is applied before `ORDER BY` is applied.
{% endhint %}

<sub>*This page is: Copyright © 2025 MariaDB. All rights reserved.*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/analytics/mariadb-columnstore/reference/data-manipulation-statements/columnstore-select.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.