Often in PostgreSQL you may want to select the row with the max length in a specific field.
You can use the following syntax to do so:
SELECT *, length(team) AS team_length
FROM athletes
WHERE length(team) = (SELECT max(length(team)) FROM athletes);
This particular example will select the row with the max length in the team field of the athletes table.
Note: If multiple rows share the max length in a specific field then each of those rows will be returned.
The following example shows how to use this syntax in practice.
Example: How to Select Row with Max Length in PostgreSQL
Suppose that we create the following table named athletes that contains information about 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, 'Hawks', 40);
INSERT INTO athletes VALUES (0003, 'Rockets', 31);
INSERT INTO athletes VALUES (0004, 'Magic', 25);
INSERT INTO athletes VALUES (0005, 'Grizzlies', 14);
INSERT INTO athletes VALUES (0006, 'Celtics', 26);
INSERT INTO athletes VALUES (0007, 'Lakers', 17);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+-----------+--------
1 | Mavs | 22
2 | Hawks | 40
3 | Rockets | 31
4 | Magic | 25
5 | Grizzlies | 14
6 | Celtics | 26
7 | Lakers | 17
Suppose that we would like to select the row with the max length in the team field.
We can use the following syntax to do so:
SELECT *, length(team) AS team_length
FROM athletes
WHERE length(team) = (SELECT max(length(team)) FROM athletes);
id | team | points | team_length
----+-----------+--------+-------------
5 | Grizzlies | 14 | 9
We can see that this returns the row with the team name of Grizzlies, which happens to be the longest name in the team field.
The new team_length field also shows that the team name Grizzlies has a length of 9.
Feel free to use similar syntax to select the row with the max length in a specific field of your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Return the Max of Two Dates
PostgreSQL: How to Find Maximum Value Across Multiple Columns
PostgreSQL: How to Select Rows with Max Value in Column By Group
