MySQL – Order by First Condition in WHERE Clause

likeMySQLorder-by

I have a table consists of the following fields

id, name

with the following values

1, ciro
2, test ciro
3, ciprox
4, other
5, other

i would like to get all the values that begin with "ci" or contain "ci" but show me the results before they start with ci and then the rest.

the query

select * FROM table WHERE name like 'ci%' or name like '%ci%' order by name;

I returns

1, ciro
2, test ciro
3, ciprox

I want

1, ciro
3, ciprox
2, test ciro

therefore before the rows in which name begins with "ci" and then those that contain

is there a way without using two queries to obtains the result?

Best Answer

This is quite a common problem! A case expression helps here. A query something like (untested)

select *
from table
where name like '%ci%'
order by case when name like 'ci%' then 0 else 1 end, name;

should do what you want.

Related Solutions

MySQL – How to Order Query by Specific Letter Using ORDER BY

Have a look at below example

DROP TABLE IF EXISTS products;

create table products(pname CHAR(30),pdescription CHAR(30),price DECIMAL(10,2),manufacturer CHAR(30));

INSERT INTO products VALUES
    ('Toys','These are toys',15.25,'ABC'),
    ('Dolls','These are Dolls',35.25,'PQR'),
    ('DustPan','These are DustPan',75.25,'AZD'),
    ('Doors','These are Doors',175.25,'RAZD'),
    ('TV','These are TV',11175.25,'RAZD'),
    ('Bed','These are Bed',1175.25,'ARAZD');

/** Check all data **/

SELECT * FROM products;
+---------+-------------------+----------+--------------+
| pname   | pdescription      | price    | manufacturer |
+---------+-------------------+----------+--------------+
| Toys    | These are toys    |    15.25 | ABC          |
| Dolls   | These are Dolls   |    35.25 | PQR          |
| DustPan | These are DustPan |    75.25 | AZD          |
| Doors   | These are Doors   |   175.25 | RAZD         |
| TV      | These are TV      | 11175.25 | RAZD         |
| Bed     | These are Bed     |  1175.25 | ARAZD        |
+---------+-------------------+----------+--------------+
6 rows in set (0.00 sec)

/** Order by D% **/
SELECT 
        pname, pdescription, price
    FROM
        products
    ORDER BY 
    CASE
        WHEN pname LIKE 'D%' THEN 1
        ELSE 2
    END;
+---------+-------------------+----------+
| pname   | pdescription      | price    |
+---------+-------------------+----------+
| Dolls   | These are Dolls   |    35.25 |
| DustPan | These are DustPan |    75.25 |
| Doors   | These are Doors   |   175.25 |
| Toys    | These are toys    |    15.25 |
| TV      | These are TV      | 11175.25 |
| Bed     | These are Bed     |  1175.25 |
+---------+-------------------+----------+
6 rows in set (0.00 sec)

Mysql – Am I using the ORDER BY clause incorrectly

Here is a sample in sqlfiddle-

The syntax for the select statment is the following

SELECT
    ...
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    ...

in the statement

SELECT * FROM table_name where id='2' order by 7 and '1';

7 and '1' must be interpreted as expression by the parser: 7 is the integer 7 and not a row position in the select list. The manual says

Logical AND. Evaluates to 1 if all operands are nonzero and not NULL,

so the expresion evaluates to 1

Best Answer

Related Solutions

MySQL – How to Order Query by Specific Letter Using ORDER BY

Mysql – Am I using the ORDER BY clause incorrectly

Related Question