sql ORDER BY multiple values in specific order

Question

Ok I have a table with a indexed key and a non indexed field   I need to find all records with a certain value and return the row  I would like to know if I can order by multiple values   Example   id     x field --     ----- 123    a 124    a 125    a 126    b 127    f 128    b 129    a 130    x 131    x 132    b 133    p 134    p 135    i   pseudo  would like the results to be ordered like this  where ORDER BY x field    f    p    i    a   SELECT   FROM table WHERE id NOT IN  126  ORDER BY x field  f    p    i    a    So the results would be   id     x field --     ----- 127    f 133    p 134    p 135    i 123    a 124    a 125    a 129    a   The syntax is valid but when I execute the query it never returns any results  even if I limit it to 1 record  Is there another way to go about this   Think of the x field as test results and I need to validate all the records that fall in the condition  I wanted to order the test results by failed values  passed values  So I could validate the failed values first and then the passed values using the ORDER BY   What I can t do    GROUP BY  as I need to return the specific record values WHERE x field IN  f    p    i    a    I need all the values as I m trying to use one query for several validation tests  And x field values are not in DESC ASC order   After writing this question I m starting to think that I need to rethink this  LOL

User · Answer

You can order by a selected column or other expressions.

Here an example, how to order by the result of a case-statement:

  SELECT col1
       , col2
    FROM tbl_Bill
   WHERE col1 = 0
ORDER BY -- order by case-statement
    CASE WHEN tbl_Bill.IsGen = 0 THEN 0
         WHEN tbl_Bill.IsGen = 1 THEN 1
         ELSE 2 END

The result will be a List starting with "IsGen = 0" rows, followed by "IsGen = 1" rows and all other rows a the end.

You could add more order-parameters at the end:

  SELECT col1
       , col2
    FROM tbl_Bill
   WHERE col1 = 0
ORDER BY -- order by case-statement
    CASE WHEN tbl_Bill.IsGen = 0 THEN 0
         WHEN tbl_Bill.IsGen = 1 THEN 1
         ELSE 2 END,
         col1,
         col2

User · Answer

you can use position text in text  in order by for ordering the sequence

User · Answer

Use a case switch to translate the codes into numbers that can be sorted   ORDER BY   case x field   when  f  then 1   when  p  then 2   when  i  then 3   when  a  then 4   else 5   end

User · Answer

You can use a LEFT JOIN with a  VALUES   f  1    p  2    a  3    i  4   and use the second column in your order-by expression  Postgres will use a Hash Join which will be much faster than a huge CASE if you have a lot of values  And it is easier to autogenerate   If this ordering information is fixed  then it should have its own table

User · Answer

WHERE    x field IN   f    p    i    a       ORDER BY    CASE x field       WHEN  f  THEN 1       WHEN  p  THEN 2       WHEN  i  THEN 3       WHEN  a  THEN 4       ELSE 5 --needed only is no IN clause above  eg when    b     END  id

User · Answer

Since i don t have enough reputation to write as a comment  added this as a new answer  You can add asc or desc to order by clause  ORDER BY x field  A  ASC  x field  I  DESC  x field  P  DESC  x field  F  ASC  which makes I first  P second and A as last one and F before the last

User · Answer

For someone who is new to ORDER BY with CASE this may be useful  ORDER BY      CASE WHEN GRADE    A  THEN 0          WHEN GRADE    B  THEN 1          ELSE 2 END

User · Answer

I found a much cleaner solution for this   ORDER BY array position ARRAY  f    p    i    a    varchar    x field    Note  array position needs Postgres v9 5 or higher

User · Answer

The CASE and ORDER BY suggestions should all work  but I m going to suggest a horse of a different color  Assuming that there are only a reasonable number of values for x field and you already know what they are  create an enumerated type with F  P  A  and I as the values  plus whatever other possible values apply   Enums will sort in the order implied by their CREATE statement  Also  you can use meaninful value names mdash your real application probably does and you have just masked them for confidentiality mdash without wasted space  since only the ordinal position is stored

User · Answer

bobflux s answer is great  I would like to extend it by adding a complete query that uses proposed approach  select tt id  tt x field from target table as tt -- Here we join our target table with order table to specify custom ordering  left join      values   f   1     p   2     i   3     a   4   as order table  x field  order num      on order table x field   tt x field order by     order table order num  -- Here we order values by our custom order      tt x field             -- Other values can be ordered alphabetically  for example   Here is complete demo

User · Answer

Try   ORDER BY x field  F   x field  P   x field  A   x field  I    You were on the right track  but by putting x field only on the F value  the other 3 were treated as constants and not compared against anything in the dataset

[sql] sql ORDER BY multiple values in specific order?

Examples related to sql

Examples related to postgresql

Examples related to sql-order-by