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?

CodePudding user response：

You can use a Common Table Expression (CTE) to apply the logic, and in the following query specify the WHERE and ORDER BY clause:

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

CodePudding user response：

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