CockroachDB Limiting Results From a Table
Introduction
In this tutorial we’ll be demonstrating using CockroachDB and limiting results sets. In most practical applications you don’t won’t to return every match of query but want to provide a limit in case the result set is very large. Please continue reading and we’ll show you a few different ways to limit your results set in CockroachDB on ObjectRocket.
Prerequisites
- You should have CockroachDB installed on your computer.
- You should have a working knowledge of SQL.
CockroachDB limiting results
Using the LIMIT
clause and the FETCH
clause you can limit the records that you will get from tables. Using the LIMIT
clause you can set a limitation of how many records that will be returned in the results. By using the FETCH
clause, you can set how many records will be fetched from the table.
Let us first create a table and insert some records that we will use in this demonstration.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE pets ( id INT PRIMARY KEY, pet_name VARCHAR (30), pet_type VARCHAR(30) ); INSERT INTO pets (id, pet_name, pet_type) VALUES ('1', 'Ollie', 'Dog'), ('2', 'Molly', 'Cat'), ('3', 'Bugs', 'Rabbit'), ('4', 'Floppy', 'Rabbit'), ('5', 'Max', 'Dog'), ('6', 'Sabre', 'Dog'), ('7', 'Sooty', 'Cat'); |
We’ve create a table pets
with 7 different pets, including fields for pet_name, pet_type, as well as an id so that each record is unique.
We’ll now show a few examples for limiting the number of records returned in the results.
CockroachDB LIMIT clause
For this example, we will select records from table pets
and will limit the records using the LIMIT clause. We’ve provided a limit of 5 for this demo.
1 2 3 4 5 6 7 8 9 | SELECT * FROM pets LIMIT 5; id | pet_name | pet_type -----+----------+----------- 1 | Ollie | Dog 2 | Molly | Cat 3 | Bugs | Rabbit 4 | Floppy | Rabbit 5 | MAX | Dog (5 ROWS) |
As you can see it only returned five pets even though there are a total of seven pets in the table.
Limit with Order By
For this second example, we will limit the records again but this time we will order the results by pet_name
using the ORDER BY
clause..
1 2 3 4 5 6 7 8 9 | SELECT * FROM pets ORDER BY pet_name LIMIT 5; id | pet_name | pet_type -----+----------+----------- 3 | Bugs | Rabbit 4 | Floppy | Rabbit 5 | MAX | Dog 2 | Molly | Cat 1 | Ollie | Dog (5 ROWS) |
NOTE: By default CockroachDB will return the records in an ascending order. If you’d like to have them in an a descended order you would just use the DESC
keyword after the column name.
As you can see we used the ORDER BY
clause and then provided the field which we wanted to order by eg ORDER BY pet_name
.
CockroachDB LIMIT OFFSET example
In this next example, we will use the OFFSET
clause along with the LIMIT
clause. The OFFSET
will skip records base on the numeric offset you provide. For our example we use an offset of two:
1 2 3 4 5 6 7 | SELECT * FROM pets OFFSET 2 LIMIT 3; id | pet_name | pet_type -----+----------+----------- 3 | Bugs | Rabbit 4 | Floppy | Rabbit 5 | MAX | Dog (3 ROWS) |
As you can see we limited the results to three and skipped the first two entries (id = 1, id = 2) using the offset of two.
CockroachDB fetch
In this example we will fetch only the first row in the table using FETCH
:
1 2 3 4 5 | SELECT * FROM pets FETCH FIRST ROW ONLY; id | pet_name | pet_type -----+----------+----------- 1 | Ollie | Dog (1 ROW) |
Now we will fetch the first five rows on the table.
1 2 3 4 5 6 7 8 9 | SELECT * FROM pets FETCH FIRST 5 ROWS ONLY; id | pet_name | pet_type -----+----------+----------- 1 | Ollie | Dog 2 | Molly | Cat 3 | Bugs | Rabbit 4 | Floppy | Rabbit 5 | MAX | Dog (5 ROWS) |
Conclusion
We hope you’ve found this article on CockroachDB and limiting the results returned in your queries. We used LIMIT, OFFSET, and FETCH to show different variations of doing so. If you don’t have a database to try this demo, we recommend setting up a free trial instance in ObjectRocket. It’s easy to get setup and connected to so you can concentrate on coding. Thanks for joining us.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started