Need Help, SQL Query...Mybe

[kozekz]

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

[bondi]

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

[kozekz]

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

[bondi]

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!

[kozekz]

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