---
schema_version: "1"
id: 3cfa6c9af6595ea230b998b2f5cc7f62ea878f23d0eb556b8f180c74b6498230
title: Set Returning Functions
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/set-returning-func/set-returning-functions.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/set-returning-functions.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 22d913e5b2e5bbaac1f9e8adb2d1f6c77cd08afb4cc0417c069ecf39ade51351
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# Set Returning Functions

This section describes functions that return multiple rows.
You can call set-returning functions in the [FROM clause](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/sql-commands/select.md#clauses) of a SQL query:

```sql
SELECT * FROM unnest(ARRAY['Mon','Tue','Wed','Thu','Fri']) AS days(day)
```

## Functions

- [`unnest`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/unnest.md) - Expands the elements of an array.
- [`external`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/external.md) - Reads data stored in an external file format from one or multiple external locations.
- [`generate_series` (Numerical)](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/generate-series.md) - Generates a series of numerical values from start to stop with a given step size.
- [`generate_series` (Time-based)](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/generate-series-time.md) - Generates a series of date or timestamp values from start to stop with a given interval step size.
- [`regexp_matches`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/regexp-matches.md) - Returns the captured groups of each match of a regular expression pattern.
- [`result_scan`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/result-scan.md) - Returns the result set of a previously executed query as a table. Available in Data 360 SQL only.

## `WITH ORDINALITY`

In SQL, all row sets are unordered by default.
Due to implementation details such as parallelization, rows might be processed and output in any order.

Sometimes, the order of rows generated by a set-returning function is important, though.
In such cases, the `WITH ORDINALITY` clause can be used to add an additional output column, assigning a sequential number to the generated rows:

```sql
SELECT *
FROM unnest(ARRAY['Mon','Tue','Wed','Thu','Fri']) WITH ORDINALITY AS days(day, ordinality)
ORDER BY ordinality
```

Results:

| day | ordinality |
| :-- | :--------- |
| Mon | 1          |
| Tue | 2          |
| Wed | 3          |
| Thu | 4          |
| Fri | 5          |

The `WITH ORDINALITY` clause is supported by most, but not all, set-returning functions.

## Related Documentation

- [SELECT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/sql-commands/select.md)
- [FROM Clause](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/sql-commands/select-from.md)