Order the same column by ascending or descending dependant on the value of another column

Question

I've got a situation where I want to order a column by ascending or descending based on the value of a second column (in essence, if column A is odd, sort column B ascending, otherwise descending). Example:

A	B
1	1
1	2
1	3
2	4
2	5
2	6
3	7
3	8
3	9

would be sorted to

A	B
1	1
1	2
1	3
2	6
2	5
2	4
3	7
3	8
3	9

Is this even possible in a single SQL query, or would I have to solve this outside of the database? I've got roughly 60 different A-values, so writing a separate query for each possible A-value isn't a viable option.

I tried using "ORDER BY b if (A%2=1, ASC, DESC)", which (unsurprisingly) didn't work.

Answer 1

In your particular case it is probably easiest to map b, for odd values of a as:

select a, b from t 
order by a, case when a%2=1 then 1 else -1 end * b

Slightly more compact and cryptic:

order by a, coalesce(nullif(a%2=1,0),-1)*b

For types where such mapping is not possible in any simple way (say string), another option is to "short-cut" either the asc or desc ordering as:

order by a
 , case when a%2=1 then '0' else b end 
 , case when a%2=0 then '0' else b end desc

Fiddle