SELECT

To return all columns from a table, use the wildcard * selector.

Since the * selector returns all columns, it is not necessary to specify the column names. The more columns a table has, the more resources are required to return the data.

Because the number of columns returned can affect performance, it is recommended to specify the columns to return, if possible.

For example, to return only the lastname column from the cycling.cyclist_name table:

To return multiple columns, separate the column names with a comma:

Columns can be renamed on return using an alias:

The SELECT statement can transform the data on return using functions. The functions are varied and can be used to manipulate the data in different ways. For example, JSON can be used to return all columns as JSON, toJson to return selected column as JSON, or CAST can be used to change the data type of a column. Distinct column values can be returned using the DISTINCT function. The number of rows with a particular column can be returned using the COUNT function. Any native or user-defined functions can be used to transform the data, such as the SUM or AVG function. Any user-defined aggregate function can be used to transform the data, such as the average function. The TOKEN function can be used to return the token value of a column.

To output selected data from a table in JSON format, use the JSON function:

Note the nested JSON structure in the output, with the column name as the key and the column value as the value.

To specify the JSON format for a particular column, enclose its name in the toJson() function:

To cast a column to a different data type, use the CAST function:

If a table has duplicate values in a column, use the DISTINCT function to return only the unique values:

You may want to count the number of rows in a column. Use the COUNT function to return the count:

To use the COUNT function with a WHERE clause, include the COUNT function in the SELECT list and the WHERE clause in the query. For example, to get the number of cyclists from Belgium:

A number of native functions can be used to transform the data. The data is transformed only on return, rather than stored in the table. These are general functions that are familiar to most people, such as the average, or AVG function:

User-defined functions can also be used to transform the data. For examples, the 'left` function can be used to return a column that is left-justified:

User-defined aggregates can also be used to transform the data. For examples, the 'average` function can be used to retrieve the average of the column cyclist_time_sec from a table:

The TOKEN function can be used to return the token value of a column. The token value is the hashed value of the partition key, thus it can only be used on the partition key column or columns. This function is useful when using the token value in a query to filter data with a WHERE clause.

To return the token value of the race_id column in the cycling.race table:

Each non-primary key column in a table has metadata associated with it. The WRITETIME function returns the timestamp of the last write to a column. The TTL function returns the time to live of a column.

It is important to note that the metadata functions are not available for all columns. For example, the metadata functions are not available for columns with a data type of counter or primary key columns. Additionally, the metadata functions are not available for columns that are part of a collection or a user-defined type that is not frozen.

To return the writetime of the firstname column in the cycling.cyclist_points table:

The TTL function returns the time to live of a column. This function is useful when using Time to Live (TTL) to expire data in a table. If a TTL is set on a column, the data is automatically deleted after the specified time has elapsed.

To return the TTL of the race_points column in the cycling.cyclist_points table:

Group by one or more columns. Condenses the selected rows that share the same values for a set of columns or values returned by a function into a group. Either one or more primary key columns or a deterministic function or aggregate can be used in the GROUP BY clause.

This query groups the rows by race_date and FLOOR(race_time, 1h), which returns the hour. The number of rows in each group is returned by COUNT(*).

You can fine-tune the display order using the ORDER BY clause. The partition key must be defined in the WHERE clause and then the ORDER BY clause defines one or more clustering columns to use for ordering. The order of the specified columns must match the order of the clustering columns in the PRIMARY KEY definition. The options for ordering are ASC (ascending) and DESC (descending).

If no order is specified, the results are returned in the stored order.

The ORDER BY clause also supports vector searches of the vector column. The result set is sorted using the Approximate Nearest Neighbor (ANN) algorithm with the supplied array values.

If a query returns a large number of rows, you can limit the number of rows returned, to limit the amount of data returned. The default limit is set to 10,000 rows, the number of rows cqlsh allows. This examples limits the rows to 3:

The PER PARTITION LIMIT option sets the maximum number of rows that the query returns from each partition. This will only apply to tables that spread across more than one partition. An example of such a table is defined here:

where the partition key is a composite of race_year and race_name.

The following query returns the top two cyclists from each partition stored:

The ALLOW FILTERING clause allows you to perform queries that require scanning all partitions, with no primary key columns specified. It should not be used in production as it can cause severe performance issues! When initially modeling your data, you should avoid using ALLOW FILTERING and instead model your data to avoid it. However, for a small dataset or for testing purposes, it can be useful. It may even help you identify where you need to add indexes to your data model.

For more information, see Allow Filtering explained.

The following query selects the birthday and nationality columns from the cyclist_alt_stats table, with the ALLOW FILTERING clause:

The query requires that all partitions are restricted, except when querying with an index. Use logic statements that identify the partition key columns with these operators:

See Partition keys for examples and instructions.

Use logic statements that identify the clustering segment. Clustering columns set the sort order of the stored data, which is nested when there are multiple clustering columns. After evaluating the partition key, the query evaluates the clustering statements in the nested order, the first (top level), second, third, and so on. All operators are supported in logical statements if the table has only one clustering column. To efficiently locate the data within the partition for tables with multiple clustering columns, the following restrictions apply:

Clustering column logic statements also support returning slices across clustering segments. The slice identifies the row that has the corresponding values and allows you to return all rows before, after, or between (when two slice statements are included).

See Clustering columns for examples and instructions.

The database allows retrieving data using a regular or static column if an index exists for the column. Otherwise, the IN clause works if ALLOW FILTERING is used in conjunction.

The ALLOW FILTERING option overrides restrictions on filtering partition columns, clustering columns, and regular columns, but can negatively impact performance, causing read latencies.