Erik Bukakis wrote:
> I just learned a lot stuff at
> http://dev.mysql.com/doc/mysql/en/sorting-rows.html including sorting by
> number-to-text, text-to-number, names, specific values, etc.
>
> However, the document didn't mention on how to sort by relevance.
>
> For instance, someone search for "Ang":
> COLUMN NAME: name
> COLUMN TYPE: varchar (255)
> DATA:
> John Ang
> La Chi Zoygote
> Anglosaxon
> Marco Polo
> Ang Fernandez
> John Pang Cuyi
>
> Query result should be:
> Ang Fernandez
> John Ang
> Anglosaxon
> John Pang Cuyi
>
> Is it possible to sort a column this way?
"This way" and only four rows in the example is a bit vague, but maybe
something like this could work for you:
$crit = 'Ang'; # test case
SELECT name
FROM t1
WHERE name like '%crit%'
ORDER BY
name != '$crit', # exact match
name not like '$crit %', # first word
name not like '% $crit', # last word
name not like '$crit%', # start of first word
name not like '% $crit%', # start of non-first word
name # alphabetically
Some of these are not in your example, I was just guessing... but you
get the idea. Each expression in the ORDER BY clause returns 0 (false)
or 1 (true). Because 0 is smaller than 1, and ASC is the default sorting
order, we want each expression to be FALSE, so that it is sorted first.
That's why I negated all expressions. An alternative would be to use
DESC after each expression.
--
Roger
