Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Data Presentation

Data Presentation

Learn how to limit, order, and offset the query output along with different types of joins.

We'll cover the following...

Using the LIMIT clause

The LIMIT command limits the number of rows returned from a query.

The LIMIT command syntax is as follows:

SELECT
  <column(s)>
FROM
  <table_name>
LIMIT <count>;
The syntax of the LIMIT clause

The <count> specifies the number of rows to return.

Using OFFSET clause

The OFFSET command is used to skip a certain number of rows before beginning to return results.

The OFFSET command syntax is as follows:

SELECT
  <column(s)>
FROM
  <table_name> 
OFFSET <number>;
The syntax of the OFFSET clause

The <number> specifies the number of rows to skip before beginning to return results.

Using LIMIT and OFFSET together

We can use both the LIMIT and OFFSET commands together to return a certain number of rows starting at a specific row.

The syntax for using the OFFSET and the LIMIT clause together is given below:

SELECT
  <column(s)>
FROM
  <table_name> 
LIMIT <count> [OFFSET] <number>;
The syntax for using OFFSET and LIMIT together

The OFFSET is optional and defaults to 0 if not supplied.

The sample data used in the example queries is given below:

Employee Table

Name

Salary

Department

John

50,000

IT

Jane

60,000

IT

Smith

30,000

HR

Brad

40,000

Sales

Tom

70,000

IT

Alex

60,000

Sales

Steve

80,000

Marketing

Rachel

40,000

IT

Mike

40,000

HR

If we wanted to return only the first five rows from a query, we’d use the following:

Press + to interact
SELECT
  id,
  name,
  salary,
  department
FROM
  Employee
LIMIT 5;

Code Example 2

On the other hand, if we wanted to return two rows starting at the 5th row, we’d use the following query:

Press + to interact
SELECT
  *
FROM
  Employee
LIMIT 2 OFFSET 5;

The ORDER BY clause

We can use the ORDER BY command to control the order in which rows are returned.

The syntax for using the ORDER BY clause is given below:

Press + to interact
SELECT
  <column(s)>
FROM
  <table_name>
ORDER BY
  <column_name> ASC | DESC;

Here, <column(s)> is the column or columns we want to retrieve from the table named <table_name>, and <column_name> is the specific column by which we want to order the retrieved data. The ASC or DESC option specifies whether the retrieved ...