How to Use the Postgres to Select First Record

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

The Postgre SELECT statement is used to return data from a database in the form of result table, also referred to as a result set. The SELECT statement can be used to retrieve partial records or all of the records from a given table in a database and can be used to select individual rows of a table or multiple rows. This tutorial will provide examples on how to use SELECT statement in Postgres to select the first row of a record. The tutorial will also provide examples on how to use the SELECT statement with the LIMIT clause, the ORDER BY clause and the FETCH clause.

Prerequisites

  • PostgreSQL must be properly installed and working on the local machine in order to execute the examples in this tutorial on how to use the SELECT statement in Postgres to select the first row of a record.

  • A basic working knowledge of PostgreSQL is required to follow the instruction in this tutorial.

The Postgres select first example

First, create the following table named “biggest_companies:”

1
2
3
4
5
CREATE TABLE biggest_companies(
id SERIAL PRIMARY KEY,
company VARCHAR(50),
market_value VARCHAR(10)
);

Next, insert the following records into the “biggest_companies” table as shown here:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO biggest_companies (company, market_value)
VALUES ('Apple','$943.57B'),
('Microsoft','$928.57B'),
('Amazon','$919.65B'),
('Alphabet','$859.25B'),
('Berkshire Hathaway Inc.','$521.35B'),
('Facebook','$511.35B'),
('Alibaba Group','$81.91B'),
('TenCent Holdings Ltd','$480.78B'),
('Johnson & Johnson','$369.1B'),
('JP Morgan Chase','$363.75B'),
('Visa','$363.47B'),
('Exxon Mobil','$344.83B'),
('Samsung Electronics Co., Ltd.','$313.095B'),
('Industrial and Commercial Bank of China','$301.91B'),
('Walmart','$295.00B');

Now execute the following SELECT * FROM command, as shown below, to select the records from the “biggest_companies” table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT * FROM biggest_companies;
 id |                 company                 | market_value
----+-----------------------------------------+--------------
  1 | Apple                                   | $943.57B
  2 | Microsoft                               | $928.57B
  3 | Amazon                                  | $919.65B
  4 | Alphabet                                | $859.25B
  5 | Berkshire Hathaway Inc.                 | $521.35B
  6 | Facebook                                | $511.35B
  7 | Alibaba GROUP                           | $81.91B
  8 | TenCent Holdings Ltd                    | $480.78B
  9 | Johnson & Johnson                       | $369.1B
 10 | JP Morgan Chase                         | $363.75B
 11 | Visa                                    | $363.47B
 12 | Exxon Mobil                             | $344.83B
 13 | Samsung Electronics Co., Ltd.           | $313.095B
 14 | Industrial AND Commercial Bank OF China | $301.91B
 15 | Walmart                                 | $295.00B
(15 ROWS)

Selecting the first record using limit clause

The following example will now demonstrate how to use the SELECT statement in Postgres to select the first record from the table using the LIMIT clause.

1
2
3
4
5
SELECT * FROM biggest_companies LIMIT 1;
 id | company | market_value
----+---------+--------------
  1 | Apple   | $943.57B
(1 ROW)

Note that here the number of records that will be returned is one.

Selecting the first record using fetch clause

The following example demonstrates another way to select the first record from the same table using the FECTH clause:

1
2
3
4
5
SELECT * FROM biggest_companies FETCH FIRST ROW ONLY;
 id | company | market_value
----+---------+--------------
  1 | Apple   | $943.57B
(1 ROW)

Selecting the first record with order by clause

The following examples shows how to sort the records in descending order with the ORDER BY clause using the ID number and also how to select the first record from a row:

1
2
3
4
5
SELECT * FROM biggest_companies ORDER BY id DESC LIMIT 1;
 id | company | market_value
----+---------+--------------
 15 | Walmart | $295.00B
(1 ROW)

Postgres select first 10 rows

This next example shows how to select the first 10 rows from the table using the LIMIT clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * FROM biggest_companies LIMIT 10;
 id |         company         | market_value
----+-------------------------+--------------
  1 | Apple                   | $943.57B
  2 | Microsoft               | $928.57B
  3 | Amazon                  | $919.65B
  4 | Alphabet                | $859.25B
  5 | Berkshire Hathaway Inc. | $521.35B
  6 | Facebook                | $511.35B
  7 | Alibaba GROUP           | $81.91B
  8 | TenCent Holdings Ltd    | $480.78B
  9 | Johnson & Johnson       | $369.1B
 10 | JP Morgan Chase         | $363.75B
(10 ROWS)

This last example demonstrates how to select the first 10 rows from the table using the FETCH clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * FROM biggest_companies FETCH FIRST 10 ROW ONLY;
 id |         company         | market_value
----+-------------------------+--------------
  1 | Apple                   | $943.57B
  2 | Microsoft               | $928.57B
  3 | Amazon                  | $919.65B
  4 | Alphabet                | $859.25B
  5 | Berkshire Hathaway Inc. | $521.35B
  6 | Facebook                | $511.35B
  7 | Alibaba GROUP           | $81.91B
  8 | TenCent Holdings Ltd    | $480.78B
  9 | Johnson & Johnson       | $369.1B
 10 | JP Morgan Chase         | $363.75B
(10 ROWS)

Conclusion

This tutorial explained how to use SELECT statement in Postgres to select the first row of a record. The tutorial first provided a Postgres select first example and showed how to create a table, insert records into the table and then demonstrated how to use the SELECT * FROM command to select the records from the table. The article then covered how to use the SELECT statement in Postgres to select the first record from the table using the LIMIT clause. The tutorial also explained how to use the FETCH and the ORDER BY clause and then provided an example on how to select the first 10 rows from the table using the LIMIT clause and another example on how to select the first 10 rows from the table using the FETCH clause.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.