MySQL: select rows first starts with SOMETHING then containing SOMETHING-CodePudding

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?

CodePudding user response：

You can use conditional statement like 'case when'

SELECT 
  *, 
  (case when `name` like 'SOMETHING' then 1 else 0 end) as weight0,
  (case when `name` like 'SOMETHING%' then 1 else 0 end) as weight1, 
  length(name) as weight3 
FROM `tags` 
WHERE `name` like '%SOMETHING%'
ORDER BY weight0, weight1 desc,  weight3, name
LIMIT 0, 10

CodePudding user response：

MySql evaluates boolean expressions as 1 for true or 0 for false and they can be used in the ORDER BY clause:

SELECT *
FROM tags 
WHERE name LIKE '%SOMETHING%'
ORDER BY name = 'SOMETHING' DESC,
         name LIKE 'SOMETHING%' DESC,
         name LIKE '%SOMETHING%' DESC, -- this is not actually needed
         CHAR_LENGTH(name),
         name
LIMIT 0, 10;