How to Use the Postgres to Select First Record
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