MySQL sort by...

[khadgar]

Hi there,

Can anyone tell me how to sort a 'OR' list?

e.g.

SELECT * FROM table WHERE field LIKE '%first%' OR field LIKE '%second%' OR field LIKE '%third%'

first, second and third are search result and I would like to sort the results after most hits.

Any suggestions?

[Anon]

select * from t
WHERE field LIKE '%first%'
OR field LIKE '%second%'
OR field LIKE '%third%'
order by case
when field like '%first%' then 1
else 0 end +
case
when field like '%second%' then 1
else 0 end +
case
when field like '%third%' then 1
else 0 end desc

[vponz]

Try this:

SELECT *,
if(field LIKE '%first%', 1, 0) +
if(field LIKE '%second%', 1, 0) +
if(field LIKE '%third%', 1, 0) as hits
FROM table WHERE field LIKE '%first%' OR field LIKE '%second%' OR field LIKE '%third%' order by hits;

bye

[khadgar]

Thanks ... working just fine!