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:
- Then open another terminal and use the following command to start the CockroachDB SQL client.
How to Create the Sample Database
Execute the following commands to create the raizel user and userdatabase database:
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:
user \q to exit the SQL shell.
Now that the database has been created, execute the following command to create a simple table:
id INT PRIMARY KEY,
name STRING,
age INT,
accessLevel STRING,
department STRING,
title STRING
);
Now add records in the tblusers as follows:
(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:
The results should resemble the following:
+----+-------+-----+-------------+------------+------------+
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:
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:
-- 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:
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:
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:
-- 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 CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started