How to SELECT the Top 'n' Longest Strings in a SQLite Column?

Find the top "n" longest strings in a SQLite column

Let's suppose you have the following SQLite table:

+----+--------------------------+
| id | title                    |
+----+--------------------------+
|  1 | Lorem Ipsum              |
|  2 | Proin ut Ante            |
|  3 | Duis imperdiet eu libero |
|  4 | Curabitur nulla          |
|  5 | Fusce vitae              |
+----+--------------------------+
CREATE TABLE `blog_post` (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(25) NOT NULL
);

INSERT INTO blog_post(`title`)
VALUES
    ('Lorem Ipsum'),
    ('Proin ut Ante'),
    ('Duis imperdiet eu libero'),
    ('Curabitur nulla'),
    ('Fusce vitae');

To SELECT the top "n" longest strings in a SQLite column, you can simply sort the columns in descending order by LENGTH and then LIMIT the result. For example, to show only the top three results you could use the following query:

SELECT title, LENGTH(title) count
FROM blog_post
ORDER BY LENGTH(title) DESC
LIMIT 3;

This would yield the following result:

+--------------------------+-------+
| title                    | count |
+--------------------------+-------+
| Duis imperdiet eu libero | 24    |
| Curabitur nulla          | 15    |
| Proin ut Ante            | 13    |
+--------------------------+-------+

Similarly, you can use the same query to show as many results as you want simply by changing the value for the LIMIT clause.


Hope you found this post useful. It was published . Please show your love and support by sharing this post.