Using SQL query to find the ID of the question with the longest text-CodePudding

I typed the following SQL code in DB Fiddle using PostgreSQL v14 as the database engine to create the QUESTION relation.

CREATE TABLE QUESTION (
 ID TEXT PRIMARY KEY,
 SurveyScreenID TEXT,
 QuestionNumber INT,
 QuestionText TEXT
);

I wrote the following SQL query to find the ID of the question with the longest text:

SELECT ID
FROM QUESTION
WHERE length(QuestionText) = MAX(length(QuestionText));

However, the program returned a query error saying Query Error: error: aggregate functions are not allowed in WHERE.

How can I solve this issue?

CodePudding user response：

You can put a subquery in a WHERE clause, so try this.

SELECT ID, questiontext
    FROM question
    WHERE length(questiontext) =
        (SELECT max(length(questiontext)) FROM question)

CodePudding user response：

Create an index on length, allowing efficient searches: and use subquery on max length.

CREATE INDEX que_length_qt_idx ON question (length(questiontext));

SELECT ID
FROM QUESTION
WHERE length(QuestionText) = (SELECT MAX(length(QuestionText)) FROM QUESTION);