How can I select the longest text field when using GROUP BY in mysql, a la MAX()?

You need use CHAR_LENGTH instead of LENGTH

SELECT a.id, a.post_id, a.body
FROM posts a INNER JOIN
(
    SELECT post_ID, title, MAX(CHAR_LENGTH(body)) totalLength
    FROM posts
    GROUP BY post_ID, title
) b ON a.post_id = b.post_ID AND
        a.title = b.title AND
        CHAR_LENGTH(a.body) = b.totalLength

You might want to see the difference: CHAR_LENGTH( ) vs LENGTH( )

SQLFiddle Demo

select p.post_id, p.title, p.body
from posts p
inner join (
  select post_id, max(length(body)) as MaxLength
  from posts
  group by post_id
) pm on p.post_id = pm.post_id and length(p.body) = MaxLength
order by p.id

SQL Fiddle Example

Tags:

Mysql

Sql

Group By

Related