SQL, join table but only add value on the first matching occurence

Question

I need to add value but only once by matching value as can't use a group by because I need to keep category info in my table.

Here is my two tobles.

Table 1

REGION	PERIOD	some value	category
region1	1	20	a
region1	1	10	b
region2	1	30	c
region2	2	30	d
region3	2	50	a

I want to join on region and period but only with the occurence of the matching.

Table 2

REGION	PERIOD	other value
region1	1	4
region2	1	5
region3	2	6

I'm trying to obtain this result:

Result

REGION	PERIOD	some value	category	other value
region1	1	20	a	4
region1	1	10	b
region2	1	30	c	5
region2	2	30	d
region3	2	50	e	6

To recreate my dataset sample :

CREATE TABLE table_1 (
    region VARCHAR(255),
    period VARCHAR(255),
    categories VARCHAR(255),
    some_value INT
)

INSERT INTO table_name (region, period, categories, int_values)
VALUES ('region1', '1', 'a', 20),
       ('region1', '1', 'b', 10),
       ('region2', '1', 'c', 30),
       ('region2', '2', 'd', 30),
       ('region3', '2', 'a', 40);

CREATE TABLE table_2 (
    region VARCHAR(255),
    period VARCHAR(255),
    categories VARCHAR(255),
    other_values INT
)

INSERT INTO table_name (region, period, int_values)
VALUES ('region1', '1', 4),
       ('region2', '1', 5),
       ('region3', '2', 6),

Where records have the same region and period, are you assigning the "other value" to the record with the lowest category value? Could you ever have more than one record with the same values for region, period and category? — NickW, Apr 04 '23 at 11:52
The category doesn't matter, I just want to join 'other_value' to only one region by period. Yes I could have more. — user15915737, Apr 04 '23 at 12:00
So what are the rules for determining which record you want to add the other_value to? "just add it to one of the records" is not something you can code in SQL - you need to define the logic to determine which record to use, it doesn't matter what the logic is as long as it will only result in one record being selected. Remember, there's no intrinsic record order in SQL tables so saying something like "pick the first record" is meaningless — NickW, Apr 04 '23 at 12:06
I understand, then your first idea about "assigning other_value to the record with lowest categories" could work in my case. — user15915737, Apr 04 '23 at 12:13
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Apr 04 '23 at 15:02

score 1 · Answer 1 · answered Apr 04 '23 at 12:52

The following SQL should give you roughly what you need:

with join_recs as (
    SELECT T1A.region, T1A.period, T1A.categories, T1A.some_value
    FROM table_1 T1A
    WHERE T1A.categories = (SELECT MIN(categories) FROM table_1 T1B WHERE T1A.region = T1B.region AND T1A.period = T1b.period)
),
non_join_recs as (
    SELECT T1A.region, T1A.period, T1A.categories, T1A.some_value
    FROM table_1 T1A
    WHERE T1A.categories != (SELECT MIN(categories) FROM table_1 T1B WHERE T1A.region = T1B.region AND T1A.period = T1b.period)
)
SELECT JR.region, JR.period, JR.categories, JR.some_value, t2.other_values
FROM JOIN_RECS JR
LEFT OUTER JOIN table_2 T2 on JR.region = T2.region AND JR.period = T2.period
UNION
SELECT NJR.region, NJR.period, NJR.categories, NJR.some_value, null
FROM non_join_recs NJR
order by region, period, categories
;

score 1 · Accepted Answer · answered Apr 04 '23 at 14:55

If I follow you correctly, you can join both tables, then use window functions and a case expression to display the value on the "first" matching row only:

select t1.*,
    case when row_number() over(partition by t1.region, t1.period order by t1.category) = 1
        then t2.other_value
    end as other_value
from table_1 t1
left join table_2 t2 on t2.region = t1.region and t2.period = t1.period
order by t1.region, t1.period, t1.category

region	period	category	some_value	other_value
region1	1	a	20	4
region1	1	b	10	null
region2	1	c	30	5
region2	2	d	30	null
region3	2	a	40	6

fiddle

SQL, join table but only add value on the first matching occurence

2 Answers2