How to select max length column data in oracle

Question

I have data as below:

No,Code,Name
1,code1,code1 name
2,code2,This name for code2
3,code3,code3 name

I want to select the record 2, and I tried SQL as below, but I got error ORA-00934: group function is not allowed here, what can I do?

select code, name from my_mst where max(length(name)) group by name;

Answer 1

Use a subquery to identify the max length:

SELECT code, name
FROM my_mst
WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM my_mst)

This would work well with the particular sample data set you showed us. However, it has a limitation, namely that it would return multiple records if there were a tie for the max name length. If you wanted to break that tie using, e.g., the code, then you could consider using analytic functions:

SELECT t.*
FROM
(
    SELECT code, name,
        ROW_NUMBER() OVER (ORDER BY LENGTH(name) DESC, code) rn
    FROM my_mst
) t
WHERE t.rn = 1;