List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 17 1999 6:04pm
Subject:Re: Sorting by relevance
View as plain text  
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

Thread
Sorting by relevanceCU-Jobs17 Jun
  • Re: Sorting by relevanceChristian Mack17 Jun