dcsimg
Results 1 to 5 of 5

Thread: Need Help, SQL Query...Mybe

  1. #1
    Join Date
    Nov 2008
    Posts
    3

    Red face Need Help, SQL Query...Mybe

    I'm not quite sure myself.. is it my problem just can be solve using sql query, or its more complicated than that..

    Here;
    say i got PropertyTbl with attributes in it
    -Title
    -Location
    -Remarks
    -Owner

    and i'm trying to make a search using a keyword out of it..

    This is the SQL that i used,

    SELECT * FROM PropertyTbl WHERE (Title LIKE '%keywords%' OR Location LIKE '%keywords%' OR Remarks LIKE '%keywords%' OR Owner LIKE '%keywords%') ORDER by Title ASC

    And it will return any records that have at least one keyword in any of the attributes. sorted by Title

    the exact thing i want is, if the keyword appears in the Location, then i want it sorted first.. then follow by other records. its like.. search in all.. but the priority goes to Location if got match and appear first compare to other result else sort the result by Title ASC

    hope someone will understand this .. any help ? thanks

  2. #2
    Join Date
    Nov 2008
    Posts
    5
    you can try that:

    SELECT * FROM PropertyTbl
    WHERE (Title LIKE '%keywords%' OR Location LIKE '%keywords%' OR Remarks LIKE '%keywords%' OR Owner LIKE '%keywords%')
    ORDER by case when Location LIKE '%keywords%' THEN Location
    else Title end
    ASC

  3. #3
    Join Date
    Nov 2008
    Posts
    3
    Thanks.. new thing for me..
    i tried but.. it still doesn't return Location which contains keywords first..
    say in
    Title also got keywords
    Remarks also got keywords
    Owner also got keywords
    Location also got keywords

    but i want, Location that match that keywords to be display first..

  4. #4
    Join Date
    Nov 2008
    Posts
    5
    ok, now I know what you want

    please,try adding a new flag column 1 when Location is suitable else 2 and order your results accourdingly :

    SELECT * , case when Location LIKE '%keywords%' then 1 else 2 end Flag
    FROM PropertyTbl
    WHERE (Title LIKE '%keywords%' OR Location LIKE '%keywords%' OR Remarks LIKE '%keywords%' OR Owner LIKE '%keywords%')
    ORDER by Flag , ......

    let me know!

  5. #5
    Join Date
    Nov 2008
    Posts
    3
    I had no idea how to use it and I'm quite sure i had it wrong..
    this is query i used,

    SELECT * , case when Location LIKE '%keywords%' then 1 else 2 end Flag
    FROM PropertyTbl
    WHERE (Title LIKE '%keywords%' OR Location LIKE '%keywords%' OR Remarks LIKE '%keywords%' OR Owner LIKE '%keywords%')
    ORDER by Flag , 1

    i tried search with keyword klcc.. which this keyword also exists it other columns..
    and below is the result of Location column.

    Location
    Golden Triangle
    Golden Triangle
    Golden Triangle
    Golden Triangle
    Jalan Ampang
    Bandaraya
    Titiwangsa
    Duta
    Golden Triangle
    KLCC
    Golden Triangle
    Golden Triangle
    Jalan Ampang
    Golden Triangle
    KLCC
    Bukit Bintang
    KLCC
    KLCC
    KLCC
    KLCC
    KLCC
    Ulu Klang
    Ulu Klang
    Wangsa
    Setapak
    Wangsa
    Ulu Klang
    Ulu Klang
    Ulu Klang
    Ulu Klang

    KLCC is nowhere near top..

    i hope it will look like this..

    Location
    KLCC
    KLCC
    KLCC
    KLCC
    KLCC
    KLCC
    KLCC
    Golden Triangle
    Golden Triangle
    Golden Triangle
    Golden Triangle
    Jalan Ampang
    Bandaraya
    Titiwangsa
    Duta
    Golden Triangle
    Golden Triangle
    Golden Triangle
    Jalan Ampang
    Golden Triangle
    Bukit Bintang
    Ulu Klang
    Ulu Klang
    Wangsa
    Setapak
    Wangsa
    Ulu Klang
    Ulu Klang
    Ulu Klang
    Ulu Klang

Tags for this Thread

Posting Permissions

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