Question 21260 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: select rows first starts with SOMETHING then containing SOMETHING

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search