# SELECT (FROM MATERIALIZED VIEW)

## Syntax <a href="#synopsis" id="synopsis"></a>

```sql
SELECT
   expression AS alias [, ...]
FROM relation_name
[WHERE where_clause]
[GROUP BY group_by_clause]
[HAVING having_clause]
[ORDER BY order_by_clause [ASC | DESC] [NULLS [FIRST | LAST]] ]
[LIMIT number];
```

## Description

The SELECT statement is used to retrieve data from a [#\_materialized\_view](https://docs.deltastream.io/overview/core-concepts/databases#_materialized_view "mention").

### Arguments <a href="#parameters" id="parameters"></a>

#### FROM \<relation\_name>

The `FROM` clause specifies the source Materialized View for the query. A query on a Materialized View can refer to a single Materialized View. Currently, join is not supported between a Materialized View and other relations. The Materialized View name can be specified as a fully, or partially, qualified name via specifying the database\_name and/or schema\_name in the format `[<database_name>.<schema_name>.]<materialized_view_name>` (for example,`db1.public.pagevisits`). Otherwise, the current Database and Schema will be used to identify the Materialized View.

#### WHERE \<where\_clause>

Records can be filtered according to supplied predicates in the `WHERE` clause. Predicates are specified as Boolean expressions, which can use columns from the source Materialized View.

#### GROUP BY \<group\_by\_clause>

This clause allows grouping of records according to their values for a given set of columns. Then, one or more aggregations can be computed on records in each group. The `GROUP BY` clause in the query defines a list of one or more columns (separated by `,`) as grouping columns. Aggregate expressions are specified among `SELECT` expressions. Supported aggregate functions on Materialized Views are: `count`, `min`, `max`, `sum`, and `avg`.

#### HAVING \<having\_clause>

This clause specifies aggregate filter conditions. The `HAVING` clause can only be used with `GROUP BY` to filter out groups of records that do not satisfy a condition. Any given column reference in the `HAVING` clause has to refer to a column from the group by columns list, or it should be referred within an aggregate function.

#### ORDER BY \<order\_by\_clause>

The `ORDER BY` clause defines a sorting order on the query’s result, based on a list of one or more columns (separated by `,`). Beside ordering columns (which is a required argument), `ORDER BY` accepts two optional arguments:

* `ASC` or `DESC`: This option sets the sort direction to ascending (smaller value first) or descending (larger value first). `ASC` is the default sorting order.
* `NULLS FIRST` or `NULLS LAST`: This option specifies whether a result row, which has the `NULL` value in the `ORDER BY` column(s), appears before or after the rows with non-null value(s) for the same column(s) in the sorted results. The default value for this option depends on the sort direction: `NULLS LAST` is the default for the `ASC` sort order, and `NULLS FIRST` is the default for the `DESC` order.

#### LIMIT \<number>

This clause specifies a constraint on the maximum number of rows that a query retrieves from its results. It enables fetching just a portion of the query’s results on a Materialized View. Its argument is a positive number, which defines the limit count, and no more than that many rows will be fetched from the query’s result.

Currently, a `SELECT` statement on a Materialized View has an enforced default limit of 1000 records on the results, and a `LIMIT` clause cannot override this limit to set it to a larger number.

## Examples

For the following examples, assume two Materialized Views named `pagevisits` and `usersinfo have` been created by the following statements on `pageviews` and `users` streams:

```sql
CREATE STREAM pageviews (
  viewtime BIGINT,
  userid VARCHAR,
  pageid VARCHAR
) WITH (
  'topic' = 'pageviews',
  'value.format' = 'json'
);
```

```sql
CREATE STREAM "users" (
  registertime BIGINT,
  userid VARCHAR,
  regionid VARCHAR,
  gender VARCHAR,
  interests ARRAY<VARCHAR>,
  contactinfo STRUCT<phone VARCHAR, city VARCHAR, "state" VARCHAR, zipcode VARCHAR>
) WITH (
  'topic'='users',
  'value.format' = 'json'
);
```

```sql
CREATE MATERIALIZED VIEW
  pagevisits
AS SELECT
  pageid, count(userid) AS cnt 
FROM pageviews
GROUP BY pageid;
```

```sql
CREATE MATERIALIZED VIEW
  userinfo
AS SELECT
  userid,
  gender,
  regionid AS district,
  contactInfo->city AS area, 
  interests[1] AS hobby 
FROM "users";
```

#### Select all

The following statement selects all records from the `pagevisits` Materialized View.

```sql
SELECT * FROM pagevisits;
```

#### Filter records

The following statement selects all records from the `pagevisits` Materialized View that have more than 5 view counts. In the results, `pageid` and `cnt` columns are represented as `pgid` and `view_count` respectively.

```sql
SELECT
    pageid AS pgid, cnt AS view_count
FROM pagevisits
WHERE cnt > 5;
```

#### Order and limit results <a href="#having_clause" id="having_clause"></a>

The following statement selects top-10 records from the `pagevisits` Materialized View according to their total view counts. Pages with the `NULL` value for the `cnt` column are shown last.

```sql
SELECT
    *
FROM pagevisits
ORDER BY cnt DESC
NULLS LAST
LIMIT 10;
```

#### Run grouping and aggregation <a href="#having_clause" id="having_clause"></a>

The following statement groups records in the `userinfo` Materialized View according to their `userid` column and returns the total count of records in each group.

```sql
SELECT
    userid, count(*) AS user_count
FROM userinfo
GROUP BY userid;
```


---

# 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://docs.deltastream.io/reference/sql-syntax/query/materialized-view/select-from-materialized-view.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.