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 # alphabeticallySome 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
