PostgreSQL: How to Select First and Last Record from Query

Often in PostgreSQL you may want to select only the first and last record from a query.

You can use the following syntax to do so:

(SELECT *
FROM athletes
ORDER BY points DESC
LIMIT 1)

UNION ALL

(SELECT *
FROM athletes
ORDER BY points ASC
LIMIT 1);

This particular example orders the rows of the athletes table based on the values in the points column and returns only the first and last row.

Here is how this syntax works:

• First, we select all rows from the table ordered in ascending order by the points column. We use the LIMIT 1 clause to only return the first row (which has the max points value).
• Then, we select all rows from the table ordered in descending order by the points column. We use the LIMIT 1 clause to only return the first row (which has the minimum points value).
• Lastly, we use UNION ALL to join these two rows together into one table. 

The following example shows how to use this syntax in practice.

Example: How to Select First and Last Record from Query in PostgreSQL

Suppose that we create the following table named athletes that contains information about points scored by various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);

-- view all rows in table
SELECT * FROM athletes;

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  3 | Mavs |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 | Heat |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     16
 10 | Heat |     33

Suppose that we would like to order the rows based on the values in the points column and only return the first and last row, i.e. the player with the least points and the player with the most points.

We can use the following syntax to do so:

(SELECT *
FROM athletes
ORDER BY points DESC
LIMIT 1)

UNION ALL

(SELECT *
FROM athletes
ORDER BY points ASC
LIMIT 1);

 id | team | points 
----+------+--------
  7 | Heat |     40
  4 | Mavs |      9

The resulting table contains only the player with the least points and the player with the most points.

Feel free to use similar syntax to only return the first and last records from your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: ORDER BY Multiple Values in Specific Order
PostgreSQL: How to Use ORDER BY with NULL Values Last
PostgreSQL: How to Count Occurrences of Each Value in Column