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:
1 | cockroach start --insecure --listen-addr=localhost |
- Then open another terminal and use the following command to start the CockroachDB SQL client.
1 | cockroach sql --insecure --host=localhost:26257 |
How to Create the Sample Database
Execute the following commands to create the raizel user and userdatabase database:
1 2 3 | 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:
1 | 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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 | 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:
1 | SELECT * FROM tblusers |
The results should resemble the following:
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 | -- 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- 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:
1 2 3 4 5 6 7 8 9 10 11 | -- 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:
1 2 3 4 5 6 7 8 9 10 | -- 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:
1 2 3 4 5 6 7 8 9 10 11 | 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 CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started