Conditional Order By in sql

Question

I have a sample data, which I want to sort. If User is 1,then sort views in descending, otherwise if User is not 1,then sort normally. I have written below sql,and I am getting required result. My Question is Why and How it works?

with data as (
    select 2 as User,   1 as Views UNION ALL
    select 1,3 UNION ALL 
    select 4,1 UNION ALL
    select 1,5 UNION ALL
    select 1,6 UNION ALL
    select 2,6 UNION ALL
    select 7,2 UNION ALL
    select 8,3 UNION ALL
    select 3,9
    
    )
    select ARRAY_AGG(struct(User,Views) order by if(User=1,1,0) desc   ,Views desc  ) 
    from data

I am confused with if(User=1,1,0), if User=1,then 1.Is this 1,the column number? If its column number,then ,when User is not equal to 1,then the value will be 0 ,which is not any column.
I was researching on this,and found that,if I write, if(User=1,100,0) desc ,Views desc ,then also I am getting correct result ,mean numbers in that IF() are not columns, otherwise 100 will produce error ,becoz there is no 100th column.

Can Anyone explain me,how its working? Image 1 Image 2

user_5221944 · Answer

Can Anyone explain me,how its working?

I think below is the simplest way to explain/show what is happening here

Consider below slightly modified/simplified example - I eliminated aggregation to focus on ordering aspect only

with data as (
  select 2 as user, 1 as views union all
  select 1,3 union all 
  select 4,1 union all
  select 1,5 union all
  select 1,6 union all
  select 2,6 union all
  select 7,2 union all
  select 8,3 union all
  select 3,9
)
select *, if(user=1,1,0) sort 
from data
order by sort desc, views desc

output of above is

I don't think you have any doubts why above result is as is - it is just straightforward!!

Now - if you use if(user=1,100,0) - you get

Obviously, exactly same output (in terms of ordering) and I still don't think you have any doubts why it is as it is

So, finally to streamline query - users (or at least power users) would use the shortcut - instead of introducing sort column to use in order by - they would move this into order by itself

Hope this is clear now for you!

Conditional Order By in sql

1 Answers