CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
Phill Pafford
Phill Pafford

Reputation: 85368

sql ORDER BY multiple values in specific order?

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:

After writing this question I'm starting to think that I need to rethink this, LOL!

Upvotes: 158

Views: 207403

Answers (12)

gbn
gbn

Reputation: 432611

...
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 -- fallback for values not inside the IN clause. eg : x_field = 'b'
   END, id

Upvotes: 270

Mohammad Amin Takroosta
Mohammad Amin Takroosta

Reputation: 119

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')

Upvotes: 1

Marc B
Marc B

Reputation: 360812

Try:

ORDER BY x_field='f', x_field='p', x_field='i', x_field='a'

You were on the right track, but by putting x_field only on the 'f' value, the other three were treated as constants and not compared against anything in the dataset.

Upvotes: 44

Iliar Turdushev
Iliar Turdushev

Reputation: 5213

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

Upvotes: 4

user
user

Reputation: 73

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.

Upvotes: 1

Akitha_MJ
Akitha_MJ

Reputation: 4294

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

Upvotes: 4

Code
Code

Reputation: 749

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

Upvotes: 0

rept
rept

Reputation: 2246

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.

Upvotes: 41

Mukesh Kulal
Mukesh Kulal

Reputation: 20

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

Upvotes: -2

bobflux
bobflux

Reputation: 11591

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.

Upvotes: 35

Andrew Lazarus
Andrew Lazarus

Reputation: 19360

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—your real application probably does and you have just masked them for confidentiality—without wasted space, since only the ordinal position is stored.

Upvotes: 7

Guffa
Guffa

Reputation: 700720

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

Upvotes: 21

Related Questions