CODEWITHSUNDEEP
sqlpostgresql
Lizurt
Lizurt

Reputation: 15

Is it possible to ORDER depending on which WHERE condition succeeded?

Let's say I have a table called temp which contains int attributes a and b:

create table temp (id int primary key, a int, b int);
insert into temp values(1, 1, 1);
insert into temp values(2, -1, 1);
insert into temp values(3, -1, -1);

I need to sort some SELECT results depending on which one WHERE condition succeeded. A primitive but not working example:

select *, priority from temp where (
    case 
        when a > 0 and b > 0 then 1 
        when a > 0 or b > 0 then 2 
        else 0 
    end
    as priority
) > 0 order by priority asc

It says something like "if a and b are both positive then this tuple will be one of the firstest. If at least one of the attributes is positive then this tuple comes after first ones. Else don't select the tuple"

So is it possible to save a CASE result to use it in ORDER? Or maybe there are workarounds without having to check the conditions twice in both WHERE and ORDER or making possibly expensive UNION?

Upvotes: 0

Views: 76

Answers (1)

VBoka
VBoka

Reputation: 9083

Please explain what is wrong with this(if anything is)?

select * 
from temp 
order by case 
         when a > 0 and b > 0 then 1 
         when a > 0 or b > 0 then 2 
         else 0 
         end

Just use your case when then end logic in order by clause(without using "priority" alias).

Here is a demo

Then this should be ok for you:

select * --to select all
from temp --from your table
where (a > 0 or b > 0) --where a or b is bigger than 0 because this is the same as your case when then end example
order by case --I have used your logic for order by clause
        when a > 0 and b > 0 then 1 
        when a > 0 or b > 0 then 2 
        else 0 
    end

Here is a second demo

And this is a third option suggested by OP:

select *
from temp 
where (a > 0 or b > 0)
order by case 
        when a > 0 and b > 0 then 0
        else 1
    end

Here is a third demo

Upvotes: 1

Related Questions