---
title: "GROUP BY and ORDER BY"
url: https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/groupby-orderby/
description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently"
last_updated: 2026-08-28T06:26:16Z
service: Cloud Scale
type: general
---

### GROUP BY Statement

The `GROUP BY` statement is used to group the records fetched in a search query results by one or more columns. `GROUP BY` enables identical data to be grouped together and displayed sequentially. When there are duplicate values in a column, the `GROUP BY` statement displays the duplicates together. The records are then ordered based on other columns.

The `GROUP BY` statement is associated with the `SELECT` statement and is often used with [ZCQL functions](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/zcql-functions/). It is used towards the end of the query and should therefore satisfy the `JOIN` statements and follow the `WHERE` conditions.

The basic syntax for using a `GROUP BY` statement along with the `SELECT` statement is as follows:

```sql
SELECT column_name(s) FROM parent_table_name GROUP BY column_name(s)

```

**Example**:

To view a list of theaters and their locations from the _Theaters_ table, grouped by the location of the theaters, execute the following query:

```sql
SELECT TheaterName, Location FROM Theaters GROUP BY Location 
```

This will generate the following output:
<table class="content-table">
<thead>
<tr>
<th><strong>TheaterName</strong></th>
<th><strong>Location</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>Cosmos Theater</td>
<td>Albany</td>
</tr>
<tr>
<td>FunTime Cinemas</td>
<td>Buffalo</td>
</tr>
<tr>
<td>The Express Cinemas</td>
<td>New York City</td>
</tr>
<tr>
<td>ANC Cinemas</td>
<td>Rochester</td>
</tr>
</tbody>
</table>

#### BINARYOF() Function

The `BINARYOF()` function can only be used in a `GROUP BY` statement. The function can be used in places where you wish to produce an output that is not case sensitive and considers all the values in a column of a table.

The `BINARYOF()` function can only be used on columns with **VarChar** or **Text** datatype. 

For example, consider the following table *Zylker_EMP*:

<table class="content-table" style="width:30%">
<thead>
    <tr>
        <th>Names</th>
    </tr>
</thead>
<tbody>
    <tr>
        <td>AMELIA BURROWS</td>
    </tr>
    <tr>
        <td>amelia burrows</td>
    </tr>
    <tr>
        <td>amelia Burrows</td>
    </tr>
    <tr>
        <td>Amelia Burrows</td>
    </tr>
</tbody>
</table>

An example ZCQL statement to illustrate the functionality of `BINARYOF()` and its possible output is given below:

```sql
SELECT Names FROM Zylker_EMP GROUP BY BINARYOF(Names)
```

<table class="content-table" style="width:30%">
<thead>
    <tr>
        <th>Zylker_EMP.Names</th>
    </tr>
</thead>
<tbody>
    <tr>
        <td>AMELIA BURROWS</td>
    </tr>
    <tr>
        <td>amelia burrows</td>
    </tr>
    <tr>
        <td>amelia Burrows</td>
    </tr>
    <tr>
        <td>Amelia Burrows</td>
    </tr>
</tbody>
</table>

> **Note:** 

* This functionality is only available in **ZCQL V2**.

* From **December 01st, 2024**, all your current projects in all your [Orgs](https://docs.catalyst.zoho.com/en/getting-started/catalyst-organizations/) present in the [Development Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/development-environment/) will be automatically mapped to **ZCQL V2 Parser**.

* From **April 01st, 2025**, all the projects present in all *Orgs* that have already been mapped to **ZCQL V2 Parser** in *Development Environment* will be automatically mapped to **ZCQL V2 Parser** in the [Production Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/production-environment/), if and when production is enabled for the project.

### ORDER BY Statement

The `ORDER BY` statement is used to sort the records fetched in a search query results in an ascending or a descending order, based on one or more columns. When the `ORDER BY` statement is used to sort textual data, it sorts the records in the alphabetical order.

Similar to the `GROUP BY` statement, the `ORDER BY` statement is associated with the `SELECT` statement and is often used after the `GROUP BY` statement. It is used towards the end of the query, after the `JOIN` clause statements or the WHERE conditions, if present, but before the `LIMIT` clause. The `ORDER BY` statement should then satisfy the `JOIN` statements and follow the `WHERE` conditions.

> **Note**: If the `ORDER BY` statement follows a GROUP BY statement, the `ORDER BY` statement takes precedence and the results are ordered first based on the `ORDER BY` statement, and then grouped together based on the `GROUP BY` statement.

* If there are duplicate values in a column, the `ORDER BY` statement will display the duplicates together. The records are then ordered based on other columns.

* [ZCQL Functions](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/zcql-functions/) can also be used with `ORDER BY` statements.

> **Note:** To utilize all the features of the `ORDER BY` statement, we recommend you migrate your codebase from **ZCQL V1** to **ZCQL V2**. You can learn more on the enhancements and features of **ZCQL V2** from this [help document](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/syntax-exceptions/)

The basic syntax for using a `ORDER BY` statement with the `SELECT` statement is as follows:

```sql
SELECT column_name(s) FROM parent_table_name ORDER BY column_name(s) [ASC | DESC]
```

> **Note:**

* By default, the `ORDER BY` statement sorts the records in ascending order without using the keyword '`ASC`'. To sort the records in a descending order, you will have to use the keyword '`DESC`'.

* In all columns where you use the `ORDER BY` clause, the `ASC` and `DESC` functionality can now be applied. You can also apply `ASC` and `DESC` for individual columns.

**Example**:

To view a list of the ticket prices from the Pricing table in descending order for each movie from the _Movies_ table execute the following query:

```sql
SELECT Movies.MovieName, Pricing.Price FROM Movies
INNER JOIN Pricing ON Movies.MovieID = Pricing.MovieID
ORDER BY Pricing.Price DESC 
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>MovieName</strong></th>
<th><strong>Price</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>Hotel Transylvania 3: Summer Vacation</td>
<td>11.50</td>
</tr>
<tr>
<td>The First Purge</td>
<td>9.20</td>
</tr>
<tr>
<td>Ant-Man and the Wasp</td>
<td>8.64</td>
</tr>
<tr>
<td>Skyscraper</td>
<td>7.44</td>
</tr>
</tbody>
</table>