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.

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.

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..

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:

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:

Now we will fetch the first five rows on the table.

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.