How to SELECT 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.


This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.