I want to select rows from tags table where tag name includes SOMETHING. The results must be sorted in a smart way: records which start with SOMETHING should go first, and then should go the rest.
It can be easily achieved using 2 queries: select by tag like 'SOMETHING%'
first, and then by tag like '%SOMETHING%'
. I want to combine this into one query. Is it possible? In general I want some equations which are usually used in WHERE
clause to present in FIELDS
section. For example,
SELECT
*,
(`name` like 'SOMETHING' ? 1 : 0) as weight0,
(`name` like 'SOMETHING%' ? 1 : 0) as weight1,
(match `name` against 'SOMETHING') as weight2,
length(name) as weight3
FROM `tags`
WHERE `name` like '%SOMETHING%'
ORDER BY weight0, weight1 desc, weight2 desc, weight3, name
LIMIT 0, 10
Obviously, the pseudo code above does not work, but I hope it shows the goal: moving WHERE
conditions to SELECT
area, assign numeric values, and the sort by these values. Is it somehow possible to achieve in MySQL?
2
Answers
MySql evaluates boolean expressions as
1
fortrue
or0
forfalse
and they can be used in theORDER BY
clause:You can use conditional statement like
case when