Ordering by specific field value first

Question

I have a table with 3 columns   id   name   priority --------------------  1   core      10  2   core      9  3   other     8  4   board     7  5   board     6  6   core      4   I want to order the result set using priority but first those rows that have name core even if have lower priority  The result should look like this  id   name   priority --------------------  6   core      4  2   core      9  1   core      10  5   board     6  4   board     7  3   other     8

User · Answer

One way to give preference to specific rows is to add a large number to their priority. You can do this with a CASE statement:

  select id, name, priority
    from mytable
order by priority + CASE WHEN name='core' THEN 1000 ELSE 0 END desc

Demo: http://www.sqlfiddle.com/#!2/753ee/1

User · Answer

Use this    SELECT    FROM tablename  ORDER BY priority desc  FIELD name   core

User · Answer

One way is this   select id  name  priority from table a order by case when name  core  then -1 else priority end asc  priority asc

User · Answer

SELECT   FROM cars new WHERE status    1  and car hide    1  and cname IN   Executive Car   Saloon   MPV    MPV5   ORDER BY FIELD cname   Executive Car    Saloon   MPV    mpv5

User · Answer

do this   SELECT   FROM table ORDER BY column  name  0 ASC   Appending the  0 will mean that   0  10  11  2  3  4   becomes    0  2  3  4  10  11

User · Answer

Generally you can do  select   from your table order by case when name    core  then 1 else 2 end           priority    Especially in MySQL you can also do  select   from your table order by name  lt  gt   core            priority    Since the result of a comparision in MySQL is either  0 or 1 and you can sort by that result

User · Answer

There s also the MySQL FIELD function   If you want complete sorting for all possible values   SELECT id  name  priority FROM mytable ORDER BY FIELD name   core    board    other     If you only care that  core  is first and the other values don t matter   SELECT id  name  priority FROM mytable ORDER BY FIELD name   core   DESC   If you want to sort by  core  first  and the other fields in normal sort order   SELECT id  name  priority FROM mytable ORDER BY FIELD name   core   DESC  priority   There are some caveats here  though   First  I m pretty sure this is mysql-only functionality - the question is tagged mysql  but you never know   Second  pay attention to how FIELD   works  it returns the one-based index of the value - in the case of FIELD priority   core    it ll return 1 if  core  is the value   If the value of the field is not in the list  it returns zero   This is why DESC is necessary unless you specify all possible values

User · Answer

This works for me using Postgres 9    SELECT   FROM your table ORDER BY name    core  DESC  priority DESC

[mysql] Ordering by specific field value first

Examples related to mysql

Examples related to sql

Examples related to select

Examples related to sql-order-by