I have the below table:

The dates will run until Jan of next year for both periods and it has more countries, cat1, 2, 3. This is just showing a few combinations

I like to find the score from day 1 for each groupings (period, country, cat1, cat2, cat3) and add this day 1 score as a additional column so it looks like this:

Will appreciate any help. Thank you!!

CodePudding user response：

You can use left join but get the latest score first using rownum()

select b.*, a.score
from (select *, row_number() over (partition by period, country, cat1, cat2, cat3
        order by yyyymmmdd asc) as rn from test) a
left join test b
     on b.period = a.period and b.country=a.country and a.cat1 = b.cat1
            and a.cat2 = b.cat2 and a.cat3 = b.cat3 and a.rn=1

see dbfiddle

CodePudding user response：

You may also try using subquery and JOIN.

Query:

SELECT t.yyyymmdd, t.period, t.country, t.cat1, t.cat2, t.cat3, t.score, 
       sq.score AS score_day1 
FROM t
LEFT JOIN
          (
           SELECT yyyymmdd, period, country, cat1, cat2, cat3, score 
           FROM 
               (
                SELECT yyyymmdd, period, country, cat1, cat2, cat3, score, 
                       RANK() OVER(PARTITION BY period ORDER BY yyyymmdd ASC) AS rnk
                FROM t
               ) AS r
           WHERE rnk=1
          ) AS sq
     ON t.period=sq.period 
     AND t.country=sq.country 
     AND t.cat1=sq.cat1
     AND t.cat2=sq.cat2
     AND t.cat3=sq.cat3
ORDER BY t.period, t.yyyymmdd

Explanation:

• Rank is used to find out first date for each period
• Then taking the scores and other info with rank as 1
• Then joining back to original table

See demo in db<>fiddle

CodePudding user response：

Does this answer?

SELECT yyyymmdd, period, country, cat1, cat2, cat3, score, (select score from tbl t2 where yyyymmdd=(select min(yyyymmdd) from tbl where period=t1.period) and t1.period=t2.period and t1.country=t2.country and t1.cat1=t2.cat1 and t1.cat2=t2.cat2 and t1.cat3=t2.cat3 limit 1) score_day1 from tbl t1

Here is a fiddle: https://www.db-fiddle.com/f/njLJnbNkFEdjSNUfopX7Ye/0