SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by firblazer View Post
    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
    Isn't

    2, test, testing

    longer than

    5, moo, moo1

    ?

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

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    You wouldnt put it in the where clause - it would be in the select.
    Code:
    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.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    You wouldnt put it in the where clause - it would be in the select.
    Code:
    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.
    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. #6
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT 
         id
        , name
        , CHAR_LENGTH(name) AS longest 
        , datetime
    FROM 
        myTable
    GROUP BY 
    	datetime
    ORDER BY 
    	longest 
    DESC 
    LIMIT 2
    perhaps?
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by firblazer View Post
    ...the longest grouped by date.
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    Thanks for your post. But it returned 0 rows.

    Can you review it?

    Thanks.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my apologies

    change the last line as follows --
    Code:
        AND LENGTH(t.name) = x.longest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by firblazer View Post
    Another way is the group the same datetime and also the localParty and the remoteParty.
    so add those columns to the GROUP BY clause in the subquery, and add those columns to the ON clause of the join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. Thanks a lot.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •