CODEWITHSUNDEEP
sqlpostgresqljoin
Alejandro Lee
Alejandro Lee

Reputation: 167

SQL Join on Multiple Matching Criterias

Is there a way to join only one time, even though there are multiple rows with the same matching criteria? I am only wanting to join on Name and Region but want Count to be applied to only one of the rows (No specific order).

enter image description here

Upvotes: 1

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This is a strange request, but you can use row_number():

select t2.*,
       (case when row_number() over (partition by name, region order by id) = 1
             then t1.count
        end) as count
from table2 t2 join
     table1 t1
     on t2.name = t1.name and t2.region = t1.region;

However, if you are counting the values in table2, then there is a much easier way:

select t2.*,
       (case when row_number() over (partition by name, region order by id) = 1
             then count(*) over (partition by name, region)
        end) as count
from table2 t2;

Table1 is not needed at all.

Upvotes: 2

Related Questions