How to Perform Ordering Query Results in CockroachDB

Introduction

This tutorial will explain how to execute the sorting and ordering of data in CockroachDB. The article will provide basic examples of how to sort data with simple columns, how to sort in descending order and sort data using primary key order. A basic working knowledge of SQL syntax is required for ordering query results in CockroachDB.

Prerequisites

  • A basic working knowledge of SQL syntax.

  • CockroachDB must be properly installed and configured before beginning.

  • Confirm the CockroachDB service is running in the background by executing the following command:

cockroach start --insecure --listen-addr=localhost
  • Then open another terminal and use the following command to start the CockroachDB SQL client.
cockroach sql --insecure --host=localhost:26257

How to Create the Sample Database

Execute the following commands to create the raizel user and userdatabase database:

CREATE USER IF NOT EXISTS yeshua;

CREATE DATABASE userdatabase;

Enter the command SELECT DATABASE = userdatabase; to call up the database.

For the purpose of this tutorial, execute the following commands to grant the user read and write permission to the database:

GRANT ALL ON DATABASE userdatabase TO yeshua.

user \q to exit the SQL shell.

Now that the database has been created, execute the following command to create a simple table:

CREATE TABLE tblusers(
id INT PRIMARY KEY,
name STRING,
age INT,
accessLevel STRING,
department STRING,
title STRING
);

Now add records in the tblusers as follows:

INSERT INTO tblusers (id, name, age, accessLevel, department, title) VALUES
(1, 'mark', 22, 'user', 'accounting', 'assistant'),
(2, 'gene', 24, 'user', 'research', 'rankfile'),
(3, 'don', 25, 'admin', 'ict', 'supervisor'),
(4, 'isaac', 20, 'user', 'marketing', 'assistant');

Now execute the following command to verify the table has been created:

SELECT * FROM tblusers

The results should resemble the following:

id | name | age | accesslevel | department | title
+----+-------+-----+-------------+------------+------------+
1 | mark | 22 | USER | accounting | assistant
2 | gene | 24 | USER | research | rankfile
3 | don | 25 | admin | ict | supervisor
4 | isaac | 20 | USER | marketing | assistant
(4 ROWS)

How to Sort Data with Simple Columns in CockroachDB

The below SQL statement will sort the data in the tblusers using a non-unique column:

-- This will sort rows using the position of a column that is 3 and "age"
SELECT * FROM tblusers ORDER BY 3;

-- As shown below, the youngest person is displayed at the top because the rows were sorted using the “age” column:

id | name | age | accesslevel | department | title
+----+-------+-----+-------------+------------+------------+
4 | isaac | 20 | USER | marketing | assistant
1 | mark | 22 | USER | accounting | assistant
2 | gene | 24 | USER | research | rankfile
3 | don | 25 | admin | ict | supervisor
(4 ROWS)

The following example is executed with the use of an AS alias:

-- This will rename the “age” column to “identifier” due to the use of AS alias.
-- The data will then be sorted out using the identifier column.

SELECT id AS identifier FROM tblusers ORDER BY identifier;

-- The column was renamed and was sorted out in ascending manner
identifier
+------------+
20
22
24
25
(4 ROWS)

How to Sort in DESC descending order

The sorting order used by CockroachDB is in ASC ascending manner by default. The examples in this section will demonstrate how to perform the task in descending order.

The following sql statement will perform a simple sorting in descending order:

-- This command will select all rows in the table tblusers and then display the result in descending order by age.

SELECT * FROM tblusers ORDER BY age DESC;

id | name | age | accesslevel | department | title
+----+-------+-----+-------------+------------+------------+
3 | don | 25 | admin | ict | supervisor
2 | gene | 24 | USER | research | rankfile
1 | mark | 22 | USER | accounting | assistant
4 | isaac | 20 | USER | marketing | assistant
(4 ROWS)

How to Sort Data Using Primary Key Order

This type of sorting ORDER BY PRIMARY KEY notation ensures data will be presented in a primary key order, as shown here:

-- This command will sort the rows by order of the primary of the table tblusers
SELECT * FROM tblusers ORDER BY PRIMARY KEY tblusers;

id | name | age | accesslevel | department | title
+----+-------+-----+-------------+------------+------------+
1 | mark | 22 | USER | accounting | assistant
2 | gene | 24 | USER | research | rankfile
3 | don | 25 | admin | ict | supervisor
4 | isaac | 20 | USER | marketing | assistant
(4 ROWS)

The following code will perform a DESC order:

SELECT * FROM tblusers ORDER BY PRIMARY KEY tblusers DESC;

-- By just appending the DESC clause causes the order of the result to dipslay in a descending order.

id | name | age | accesslevel | department | title
+----+-------+-----+-------------+------------+------------+
4 | isaac | 20 | USER | marketing | assistant
3 | don | 25 | admin | ict | supervisor
2 | gene | 24 | USER | research | rankfile
1 | mark | 22 | USER | accounting | assistant
(4 ROWS)

Conclusion

The tutorial demonstrated the basic ways of ordering query results in CockroachDB. The article showed how to sort data with simple columns, how to sort in descending order and sort data using primary key order. Remember that the sorting order used by CockroachDB is in ASC ascending manner by default and the DESC clause must be appended for the order of the result to display in descending order.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed Redis,
MongoDB & Elasticsearch

Get Started

OR

Try CockroachDB
in Beta

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.