SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Hybrid View

  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,494
    Mentioned
    161 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,804
    Mentioned
    157 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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 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"

  7. #7
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 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"

  9. #9
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 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.....


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
  •