Often in PostgreSQL you may want to order the rows in a table based on the length of a string in a specific column.
You can use the following syntax to do so:
SELECT *
FROM athletes
ORDER BY length(team);
This particular example will order the rows in the athletes table based on the length of the string in the team column from shortest to longest.
Note: To order the rows based on length of string from longest to shortest, you can use length(team) DESC instead.
The following example shows how to use this syntax in practice.
Example: How to Order Rows by String 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 order the rows in this table based on string length in the team column.
We can use the following syntax to do so:
SELECT *
FROM athletes
ORDER BY length(team);
id | team | points
----+-----------+--------
1 | Mavs | 22
2 | Hawks | 40
4 | Magic | 25
7 | Lakers | 17
3 | Rockets | 31
6 | Celtics | 26
5 | Grizzlies | 14
We can see that this orders the rows in the athletes table based on the length of the string in the team column from shortest to longest.
To order by string length from longest to shortest, we can use the following syntax:
SELECT *
FROM athletes
ORDER BY length(team) DESC;
id | team | points
----+-----------+--------
5 | Grizzlies | 14
6 | Celtics | 26
3 | Rockets | 31
7 | Lakers | 17
4 | Magic | 25
2 | Hawks | 40
1 | Mavs | 22
We can see that this orders the rows in the athletes table based on the length of the string in the team column from longest to shortest.
Feel free to use whichever method you prefer.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Row with Max Length in Specific Field
PostgreSQL: How to Find Maximum Value Across Multiple Columns
PostgreSQL: How to Select Rows with Max Value in Column By Group
