How to find the longest text in records and show it?

Databases
1

Hi!

I have these records:

id, name
1, test
2, test, testing
3, test, testing, testing a lot
4, moo
5, moo, moo1

And my result should be:

3, test, testing, testing a lot
5, moo, moo1

How should I put the MAX(LENGTH(name))in the WHERE clause?

Thanks a lot

2

Isn’t

2, test, testing

longer than

5, moo, moo1

?

What is the logic behind the result you want to get?

3

Sorry. I forgot about the datetime.

The table again:

id, name, datetime
1, test, 2012-10-25 14:00:00
2, test, testing, 2012-10-25 14:00:00
3, test, testing, testing a lot, 2012-10-25 14:00:00
4, moo, 2012-10-25 14:01:00
5, moo, moo1, 2012-10-25 14:01:00

The grouped records should have the same datetime.

Please help. Thanks.

4

You wouldnt put it in the where clause - it would be in the select.


SELECT 
	 id
	, name
	, CHAR_LENGTH(name) AS longest 
FROM 
	myTable
ORDER BY 
	longest 
DESC

something along those lines.

NOTE: CHAR_LENGTH returns the field length in characters whereas LENGTH returns it in bytes.

5

No. It’s not what is required. I only want those 2 which are the longest grouped by date. Not the rest.

Please help. Thanks

6 

SELECT 
     id
    , name
    , CHAR_LENGTH(name) AS longest 
    , datetime
FROM 
    myTable
GROUP BY 
	datetime
ORDER BY 
	longest 
DESC 
LIMIT 2

perhaps?

7 
SELECT t.id
     , t.name
     , t.datetime
  FROM ( SELECT t1.datetime
              , MAX(t1.name_length) AS longest
           FROM ( SELECT t2.datetime
                       , LENGTH(t2.name) AS name_length
                    FROM daTable AS t2 ) AS t1
         GROUP
             BY t1.datetime ) AS x
INNER
  JOIN daTable AS t
    ON t.datetime = x.datetime
   AND t.name = x.longest
8

Hi r937,

Thanks for your post. But it returned 0 rows.

Can you review it?

Thanks.

9

my apologies

change the last line as follows –

    AND LENGTH(t.name) = x.longest
10

Hi r937,

Thanks a lot. But I found that some records may have the same datetime but are different. Another way is the group the same datetime and also the localParty and the remoteParty. These fields should have the same localParty and remoteParty for the same datetime.

The new table:
id, datetime, localParty, remoteParty, name
1, 2012-10-28 17:17:00, 1001, 9888, test
2, 2012-10-28 17:17:00, 1001, 9888, testing a long one
3, 2012-10-28 17:17:00, 1002, 9876, new one
4, 2012-10-28 17:18:01, 1234, 9000, next
5, 2012-10-28 17:18:01, 1234, 9000, next long one
6, 2012-10-28 17:19:00, 1001, 9888, same local and remote but diff datetime

It should return:
2, 2012-10-28 17:17:00, 1001, 9888, testing a long one
3, 2012-10-28 17:17:00, 1002, 9876, new one
5, 2012-10-28 17:18:01, 1234, 9000, next long one
6, 2012-10-28 17:19:00, 1001, 9888, same local and remote but diff datetime

Please help. Thanks in advance.

11

so add those columns to the GROUP BY clause in the subquery, and add those columns to the ON clause of the join

12

OK. Thanks a lot.