ascending or descending order according to boolean column

Question

This is the table structure in PostgreSQL 10.

Even though "date" is an int, it represents a yyyy mm dd date. I am trying to write a SELECT that orders by date and when it is BC=true it is desc, so dates will be in the right order -500 01 02 then -500 01 03 (yyyy mm dd)
and when is is BC=false it is asc, so dates will be in the right order again 1500 01 02, then 150 01 03 (yyyy mm dd)

I came up with this SELECT * FROM test ORDER BY bc=true desc, bc=false asc; that does great on BC dates, but it flips the AD dates (15000103 then 15000102, that is wrong).

I know there is the date type available, but I want this to work as a hack for exact BC dates.

How can I change my SELECT to properly order the dates according to the BC boolean column?

Thanks

Solution

I don't think bc=true desc, bc=false asc is working good. I'll check again when I have my computer and update my answer later.

Maybe my solutions is just a trick or a cheat. This is not a legal way. You can try this.

SELECT * FROM test 
ORDER BY bc DESC, CASE WHEN bc THEN date*(-1) ELSE date END ASC;

Or

SELECT * FROM test 
ORDER BY bc DESC, CASE WHEN bc THEN abs(date) ELSE date END ASC;

Hopefully my answer will satisfy you.

Comments

[Vao Tsun]

neat solution, especially given the time to responce

[codebot]

Are you multiplying the whole date column by -1 ? I am trying for something like pseudocode : SELECT * FROM test ORDER BY date IF bc=true THEN desc ELSE asc; but I keep getting syntax errors, no matter what I try

[codebot]

Ah, very smart soluton, but, lets say I have -5000102 and 5000102 and do SELECT * FROM test ORDER BY abs(date), bc desc;, I get -5000102 5000102 -5000103 5000103, so again, no. If the table had those exact fixed values, then yes, but the table could have any value

[codebot]

btw, it works, I just think that is strange to multiply everything with -1. What if I had 20.000 rows? I am just trying to syntax something purely based on order. But I keep getting errors. I tried nested select, union...Nothing works. This is killing me

[codebot]

Check my answer if you like. The difference with my solution is that I dont use *(-1). I am no expert to tell if any of the solutions, is better or worst, but I am suggesting another solution. Thanks

[codebot]

Thanks for the answer. The problem is that if you add another date like -5000102 they will not order properly. Plus, the union does not guarantee the proper order of both results, the comments in the other question are right. So, check my answer if you like, I came up with another solution.

[Mabu Kloesen]

So sorry, I'm out of my computer since yesterday. For your answer, no it is not multiply with (-1) for all data, only the data with bc = true, I've already put it in CASE WHEN phase. Or you can replace date*(-1) with abs(date) as suggestion of @VaoTsun in the answer below. From my experience, PostgreSQL do not have condition in Ordering, you must pre-process the data first then sort it by ASC or DESC as you want.

[Mabu Kloesen]

Yes, it's work. But I have some worried, because with this case, it will check for 4 conditions and then sort 4 times. I don't think it will be good if the data is larges. BTW, you can try it. Besides, we can talk more about this case, with Case phase, if you don't define the else phase, it will be null and happily the default of ASC is nulls last, and vice versa for DESC. You can check more about Query Order here postgresql.org/docs/10/static/queries-order.html

[Mabu Kloesen]

@codebot: I think he want to show another way to combine with my answer above. From that the query will be: SELECT * FROM test ORDER BY bc DESC, CASE WHEN bc THEN abs(date) ELSE date END ASC;