sql ORDER BY multiple values in specific order?
Solution 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
Solution 2
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.
Solution 3
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.
Solution 4
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.
Solution 5
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
Phill Pafford
Love development with PHP/Symfony/PHPStorm, iOS, PostgreSQL, Linux flavor Ubuntu, jQuery/Mobile, Foundation CSS, GitFlow AVH and HTML5 Personal Projects are Crypto Currencies, Home Automation, Mobile development, SMS/MMS and DIY electronics via Make and Hack A Day https://keybase.io/phillpafford https://onename.com/phillpafford #bitcoin: https://www.coinbase.com/phillpafford #DogeCoin: D67fwUKwKQQeL9pdbZmbWcevuAYW8XPqyz
Updated on July 25, 2022Comments
-
Phill Pafford almost 2 years
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!
-
ZygD about 13 yearsDoes Postgres support implicit boolean? If so, how does boolean sort?
-
Phill Pafford about 13 yearsI liked this solution but it didn't return anything again.
-
Andrew Lazarus about 13 years@gbn, Yes and false<true. I would have expected this to work.
-
jnns over 12 yearsThis is by far the most elegant solution!
-
igo about 8 yearsYes, it works event in Postgres. Note to this solution is that you get result sorted in reverse order. So you have to put fields in reverse order: ORDER BY x_field='A', x_field='I', x_field='P', x_field='F',
-
bigsee over 5 yearsImportant to note that array_position() is only available in Postgres v9.5 onwards, afaik.
-
Samuel Philipp about 5 yearsWhile this code snippet may solve the problem, it doesn't explain why or how it answers the question. Please include an explanation for your code, as that really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.
-
Paul Watson about 5 yearsthanks, much preferred this to the CASE methods. Works with integers too;
array_position(ARRAY[1, 0]::integer[], x_field)
-
Iliar Turdushev over 3 yearsIf someone needs a complete query that uses proposed approach, I added it here.
-
Davide almost 3 yearsTo reverse and get the correct order you can also add the (!=) operator: ORDER BY x_field!='f', x_field!='p', x_field!='i', x_field!='a'