SQL to get latest value

[lonk]

Customers often moved their branches and changed their products for sale.
From the historical records of table, CustomerLocation :

ID	EffectiveDate	CustomerCode	CustomersBranchCode	Location	Product
1	01/01/22	A	1	Auckland	Carrot
2	02/01/22	A	1	Christchurch	Kale
3	03/01/22	A	1	Dunedin	Carrot
4	04/01/22	A	2	Queenstown	Kale
5	05/01/22	A	2	Wellington
6	06/01/22	B	1	Dunedin	Kale
7	07/01/22	B	1	Christchurch
8	08/01/22	B	2	Dunedin	Dunedin
9	09/01/22	C	1	Queenstown	Kale
10	10/01/22	C	1	Auckland

How can I get result of latest updates like this ?

CustomerCode	CustomersBranchCode	Location	Product
A	1	Dunedin	Carrot
A	2	Wellington	Kale
B	1	Christchurch	Kale
B	2	Dunedin	Carrot
C	1	Auckland	Kale

I could do only 1 step as follows and it was not correct.

SELECT DISTINCT "CL"."CustomersBranchCode", 
FIRST_VALUE ( "CL"."Location" ) 
OVER ( PARTITION BY "CL"."CustomersBranchCode" 
ORDER BY CASE WHEN "CL"."Location" IS NOT NULL 
THEN "EffectiveDate" END DESC ) "CL01", 
FIRST_VALUE ( "CL"."Product" ) 
OVER ( PARTITION BY "CL"."CustomersBranchCode" 
ORDER BY CASE WHEN "CL"."Product" IS NOT NULL 
THEN "EffectiveDate" END DESC ) "CL02" 
FROM "CustomerLocation" "CL"

Can you please help edit my SQL statement with great thanks ?

0040FirstValueOf2Columns.odb (3.4 KB)

LibreOffice:
Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.1
Calc: threaded

Base: Firebird

OS: Ubuntu 22.04LTS

[sokol92]

Usual way:

select "CustomerCode", "CustomersBranchCode", "Location", "Product"
  from "CustomerLocation" f
  where f."EffectiveDate"=
  (
    Select max("EffectiveDate") from "CustomerLocation" g 
      where g."CustomerCode"=f."CustomerCode" and g."CustomersBranchCode"=f."CustomersBranchCode"
  )

[cpb]

it looks like null products should not be considered.
this is probably the most efficient way to do it.

select
	"CustomerCode", "CustomersBranchCode", "Location", "Product"
from
(
	select
		c.*,
		row_number() over(partition by "CustomerCode", "Product" order by "EffectiveDate" desc) r
	from
		"CustomerLocation" c
	where
		"Product" is not null
)
where r = 1

EDIT:
I ran the code posted by F3KTotal which produces the exact desired result.
it made me realise that I did not fully understand the issue.
below is Firebird compatible SQL.
you may or may not choose to use this code, either way please mark the answer given by F3KTotal as the solution.

with t as
(
	select
		"CustomerCode", "CustomersBranchCode", "Location", "Product",
		row_number() over(partition by "CustomerCode", "CustomersBranchCode" order by "EffectiveDate" desc) r
	from
		"CustomerLocation"
)
select
	t1."CustomerCode", t1."CustomersBranchCode", t1."Location", coalesce(t1."Product", t2."Product")
from
	(select * from t where r = 1) t1
left join
	(select "CustomerCode", "CustomersBranchCode", "Product" from t where r = 2) t2
	on t1."Product" is null and t1."CustomerCode" = t2."CustomerCode" and t1."CustomersBranchCode" = t2."CustomersBranchCode"

[F3KTotal]

SELECT
    "CustomerLocation"."EffectiveDate",
    "CustomerLocation"."CustomerCode",
    "CustomerLocation"."CustomersBranchCode",
    "CustomerLocation"."Location",
    "Q3"."Product"
FROM
    "CustomerLocation"
RIGHT JOIN
    (
    SELECT
        "CustomerLocation"."CustomerCode",
        "CustomerLocation"."CustomersBranchCode",
        "CustomerLocation"."Product"
    FROM 
        (
            SELECT MAX( "EffectiveDate" ) "MAXDATE",
            "CustomerCode",
            "CustomersBranchCode"
            FROM "CustomerLocation"
            WHERE "Product" IS NOT NULL
            GROUP BY
            "CustomerCode",
            "CustomersBranchCode" 
        )"Q2"
LEFT JOIN
    "CustomerLocation"
    ON "CustomerLocation"."EffectiveDate" = "Q2"."MAXDATE"
    AND "CustomerLocation"."CustomerCode" = "Q2"."CustomerCode"
    AND "CustomerLocation"."CustomersBranchCode" = "Q2"."CustomersBranchCode")"Q3"
    ON "CustomerLocation"."CustomerCode" = "Q3"."CustomerCode"
    AND "CustomerLocation"."CustomersBranchCode" = "Q3"."CustomersBranchCode" 
RIGHT JOIN 
    (
    SELECT
        MAX( "EffectiveDate" ) "MAXDATE",
        "CustomerCode",
        "CustomersBranchCode"
    FROM
        "CustomerLocation"
    GROUP BY
    "CustomerCode",
    "CustomersBranchCode"
    )"Q1"

ON "Q1"."MAXDATE" = "CustomerLocation"."EffectiveDate"
AND "Q1"."CustomerCode" = "CustomerLocation"."CustomerCode"
AND "Q1"."CustomersBranchCode" = "CustomerLocation"."CustomersBranchCode"