how to get the rank field with more than one source in sql-CodePudding

could you, please, help me with SQL-query: I need to add the additional column with rank_2 which will show whether the source has more than 1 source_ids.

So, I need to get this result:

I don't know how to write a query to get rank_2, which will contain 1 when the source has the only source_id (like the source "b") and contain 2 when the source has 2 or more source_ids (like the source "a").

CodePudding user response：

Assuming window functions are available you can use a query like so:

select *
     , case when count(*) over (partition by date, source_name) = 1 then 1 else 2 end as rank_2
from t

CodePudding user response：

With the count function you will be able to resolve this problem:

, count(source_id) as rank_2

Don't forget to group by date, source_name, source_id.

I only don't understand why source_id 11 needs to get value 1 as it is also shown two times in the table.