CU-Jobs wrote:
>
> Hello,
>
> I am trying to sort a result set based on the columns it was matched on but not
> having much luck.
>
> I have two fields in a database, say "movietitle" char(80) and "description" which is
> a blob.
>
> If I search on "Star Wars" I'd like all the entries match "Start Wars" in the title
> listed first and ones that only have it match it in the description listed second.
>
> How can one do this with one SQL statement?
>
> John
Hi John
Try:
SELECT
movietitle
, IF( LOCATE( 'Star Wars', movietitle), 1, 2 ) AS preferred
FROM
movies
WHERE
movietitle like '%Star Wars%'
OR description like '%Star Wars%'
ORDER BY
preffered
I also would suggest to use TEXT instead of BLOB for 'description', because with TEXT you
have case insensitive search capabilities as in CHAR or VARCHAR.
Tschau
Christian
