SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    order by KeyWord first, lenth(message)

    Code:
    data in myTable
    1 bcAA 
    2 dAAkr 
    3 AAlocpd 
    4 kkAAodcf 
    5 AA
    I have the above data in myTable, and I have the below code and the result.

    Code:
    Code 
    select message
    from myTable
    where message like binary "%AA%"
    order by length(message)
    
    result
    AA
    bcAA
    dAAkr
    AAlocpd
    kkAAodcf

    But my target result is the following.

    Code:
    target result
    AA
    AAlocpd
    dAAkr
    bcAA
    kkAAodcf
    My target result is ordered by " AA first ,lenth(message)".

    Can I make my target result?

  2. #2
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    target result
     AA
     AAlocpd
     dAAkr
     bcAA
     kkAAodcf
    You want to order by the position (from the left) of AA in the message?

  3. #3
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this (untested)

    Code:
     SELECT
     	t.Message
     ,	LOCATE('AA', t.Message) AS StringPosition
     FROM
     	MyTable t
     WHERE
     	t.Message LIKE BINARY '%AA%'
     ORDER BY
     	2
     ,	LENGTH(t.Message)
    MySQL string functions: http://dev.mysql.com/doc/mysql/en/String_functions.html

  4. #4
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Imminent
    Try this (untested)

    Code:
     
     ORDER BY
     	2
     ,	LENGTH(t.Message)

    What does it mean by "2" in "order by 2"?

    Is it the length of the KeyWord "AA"?

    If the answer is "yes",
    I am afraid because "AA" is actually a varialbe.

    when the keyword is "AA", it works fine.

    However, As I change the keyword "AA" into another, it doesn't work correctly.


    The below is the code in Coldfusion and the result.


    Code:
     Code in ColdFusion
    <cfset KeyWord="k">
    
    <cfquery datasource="DS" name="list">
    	SELECT t.Message,	
    	LOCATE('#KeyWord#', t.Message) AS StringPosition
     
       FROM MyTable3 t
       
       WHERE
     	t.Message LIKE BINARY '%#KeyWord#%'
     
      ORDER BY 1, LENGTH(t.Message)
     
    </cfquery>
    
    <cfoutput query="list">
    #message#<br>
    </cfoutput>
    
     result 
    dAAkr
    kkAAodcf
    But my target result is the following.
    Code:
    target result
    
    kkAAodcf   
    dAAkr
    I want kkAAodcf come earlier than dAAkr because "k"(first) in "kkAAodcf" comes earlier than "k"(fourth) in "dAAkr".

    first comes earlier than fourth...

    My applying of your code was something wrong?

  5. #5
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by joon
    What does it mean by "2" in "order by 2"?
    ORDER BY 2 means order by the second item in your select list. In the case above you can't use a field name since we are performing a function and want to order by the result of that function, so we have to reference it by number.

    ORDER BY 2 in the above example effectively means "Order by the result of the function LOCATE('#KeyWord#', t.Message) AS StringPosition

    This function returns a number.

    The reason your coldfusion code doesn't work is because you've changed my query to order by 1, which is ordering by the first item in the select list - t.Message - so of course you will get the wrong result.

    Your SQL in your coldfusion script should be:
    Code:
     SELECT
    	t.Message,	
    	LOCATE('#KeyWord#', t.Message) AS StringPosition
     
       FROM MyTable3 t
       
       WHERE
     	t.Message LIKE BINARY '%#KeyWord#%'
     
      ORDER BY 2, LENGTH(t.Message)

  6. #6
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I thought "2" was the length of keyword.
    That was the reason why I changed from "2" to "1" when the KeyWord is "k".

    your code works fine now.

    Thank you.


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
  •