Understanding the CockroachDB ORDER BY Clause

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you want to get the most out of your CockroachDB query, you’ll probably want your results returned in a certain order. Sorting your query results makes them easier to read and can help you gain new insights into your data. The CockroachDB ORDER BY clause can be used to sort your returned records by a specified column in different orders as well. In this tutorial, we’ll talk about the CockroachDB ORDER BY clause, and we’ll review some examples to show you how it’s used.

Prerequisites

If you’re planning to follow along with the examples shown in this tutorial, CockroachDB needs to be installed on your computer. You can access your CockroachDB database using the cockroach sql CLI. The typical \c command that is used in the PostgreSQL psql interface won’t work in the Cockroach SQL CLI; instead, try the following command:

1
USE test_db;

You’ll also need some basic CockroachDB knowledge to get the most out of this tutorial.

CockroachDB ORDER by Clause

As we mentioned earlier, the ORDER BY clause will sort the records returned from a CockroachDB query. You can sort the records in descending or ascending order by specifying DESC or ASC. If neither option is specified, the default is ascending order.

The syntax of the Cockroach ORDER BY clause is shown below:

1
SELECT * FROM TABLE_NAME ORDER BY column_name

CockroachDB CREATE TABLE Example

Before we proceed to our examples, let’s create a sample table and insert records into it:

1
2
3
4
5
6
CREATE TABLE pets (
    pet_id INTEGER PRIMARY KEY,
    name VARCHAR NOT NULL,
    gender VARCHAR NOT NULL,
    TYPE VARCHAR NOT NULL
);
1
2
3
4
5
6
7
8
INSERT INTO pets (pet_id, name, gender, TYPE)
VALUES ('1', 'Rocky', 'Male', 'Cat'),
('2', 'Bella', 'Female', 'Dog'),
('3', 'Max', 'Male', 'Dog'),
('4', 'Buddy', 'Male', 'Rabbit'),
('5', 'Molly', 'Female', 'Rat'),
('6', 'Cooper', 'Male', 'Cat'),
('7', 'Snow', 'Female', 'Rabbit');

ORDER by Clause SQL Examples

In our first example, we’ll select all records from the pets table and order them by name:

1
SELECT * FROM pets ORDER BY name;

The output of this query will look like the table shown below:

1
2
3
4
5
6
7
8
9
10
  pet_id |  name  | gender |  TYPE
---------+--------+--------+---------
       2 | Bella  | Female | Dog
       4 | Buddy  | Male   | Rabbit
       6 | Cooper | Male   | Cat
       3 | MAX    | Male   | Dog
       5 | Molly  | Female | Rat
       1 | Rocky  | Male   | Cat
       7 | Snow   | Female | Rabbit
(7 ROWS)

We can see that the records are now sorted by the name column.

CockroachDB ORDER by in Descending Order

By specifying the DESC option, we can sort the records in descending order. Let’s try our previous example again with a small change– we’ll add DESC to our query to specify descending order:

1
SELECT * FROM pets ORDER BY name DESC;

NOTE: If you’d like to order the records in ascending order instead, then just use the ASC keyword in place of DESC. You can also omit the option completely since the default option is ascending order.

The output of this SELECT statement would look like the following:

1
2
3
4
5
6
7
8
9
10
  pet_id |  name  | gender |  TYPE
---------+--------+--------+---------
       7 | Snow   | Female | Rabbit
       1 | Rocky  | Male   | Cat
       5 | Molly  | Female | Rat
       3 | MAX    | Male   | Dog
       6 | Cooper | Male   | Cat
       4 | Buddy  | Male   | Rabbit
       2 | Bella  | Female | Dog
(7 ROWS)

The records are still sorted by the pet name, but now they’re in descending order.

CockroachDB ORDER by by Multiple Columns

You can even order the records by two columns. The query will order the records by the first column specified, then it will sort by the second column specified.

The following statement will order the pets first by their gender, then by their name:

1
SELECT * FROM pets ORDER BY gender, name ASC;

Let’s see what our results look like:

1
2
3
4
5
6
7
8
9
10
  pet_id |  name  | gender |  TYPE
---------+--------+--------+---------
       2 | Bella  | Female | Dog
       5 | Molly  | Female | Rat
       7 | Snow   | Female | Rabbit
       4 | Buddy  | Male   | Rabbit
       6 | Cooper | Male   | Cat
       3 | MAX    | Male   | Dog
       1 | Rocky  | Male   | Cat
(7 ROWS)

The output confirms that our query was successful: The results were sorted first by the gender and then by the name of the pet.

Conclusion

Something as simple as sorting your query results can transform your data, making it more readable and easier to analyze. This article demonstrated how the CockroachDB ORDER BY clause can be used to sort the results of a query in different ways. If you’ve been following along with our examples, you’ll be ready to start sorting the results of your own CockroachDB queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.