# 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;
```