Sql ORDER BY multiple values ​​in a specific order?

Question

Ok I have a table with an indexed key and an indexed field. I need to find all records with a specific value and return a string. I would like to know if I can order 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 as follows: 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' 

Thus, the results will be as follows:

 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 do this?

Think of x_field as test results, and I need to check all the records that fall into state. I would like to order test results using unsuccessful values, passed values. Therefore, I could check the failed values ​​first, and then the passed values ​​with ORDER BY.

What I can not do:

• GROUP BY, since I need to return certain record values
• WHERE x_field IN ('f', 'p', 'i', 'a'), I need all the values, since 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 start to think that I need to rethink this, lol!

Answer 1

 ... 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 

Answer 2

You can use LEFT JOIN with "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's easier to auto-generate.

If this ordering information is fixed, then it should have its own table.

Answer 3

Try:

 ORDER BY x_field='F', x_field='P', x_field='A', x_field='I' 

You were on the right track, but putting x_field only on the value F, the other 3 were considered as constants and were not compared with anything in the data set.

Answer 4

Use the case switch to translate 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 

Answer 5

The CASE and ORDER BY should work, but I'm going to offer a horse of a different color. Assuming that there is only a reasonable number of values ​​for x_field and you already know what it is, create an enumerated type with F, P, A and I as the values ​​(plus any other possible values). Enumerations are sorted in the order implied by the CREATE statement. In addition, you can use meaningful names of values ​​- probably your real application and you just disguised them for privacy - without extra spaces, since only the ordinal position is preserved.

Answer 6

I found a much cleaner solution for this:

 ORDER BY array_position(ARRAY['f', 'p', 'i', 'a']::varchar[], x_field) 

Note: array_position requires Postgres v9.5 or higher.

Answer 7

You can order by selected column or other expressions.

Here is an example of how to sort 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 the lines "IsGen = 0", followed by the lines "IsGen = 1" and all other lines at the end.

You can 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 

Answer 8

You can use position (text in text) to arrange the sequence