sql ORDER BY multiple values in specific order?

user_93966 0
Viewed 168952

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!

sql-order-by postgresql sql
12 Answers

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_16105480

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_78828860

@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_128332050

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_16508090

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_97871730

if you are using MySQL 4.0 afterwards, consider using FIELD() . It returns the index position of the first argument through the next arguments and it is case-sensitive.

ORDER BY FIELD(x_field, 'f', 'p', 'i', 'a')
user_169321720

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

user_68230580
Related