MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (ID BIGINT, ITEM CHAR(9), LOCATION INT, `DATE` DATE, `VALUE` DECIMAL(6,4));
INSERT INTO test VALUES
(16711663545, '067771960', 2002, '2018-01-01', 12.5000),
(1684043432, '067771960', 2002, '2017-11-30', 10.7000),
(1684039121, '050374500', 2002, '2017-02-01', 3.2500),
(1684039321, '050374500', 2002, '2017-05-02', 3.3300),
(1684039371, '050374500', 2002, '2017-07-07', 3.8900),
(1684043441, '050374500', 2002, '2017-01-03', 4.3400),
(1684043395, '067771960', 2006, '2018-01-01', 12.5000),
(11727914496, '067771960', 2006, '2017-11-30', 10.7000),
(1684013435, '050374500', 2006, '2017-02-01', 3.2500),
(1684014435, '050374500', 2006, '2017-05-02', 3.3300),
(1684054435, '050374500', 2006, '2017-07-07', 3.8900),
(1684014215, '050374500', 2006, '2017-01-03', 4.3400);
SELECT * FROM test;


ID
ITEM
LOCATION
DATE
VALUE


16711663545
067771960
2002
2018-01-01
12.5000

1684043432
067771960
2002
2017-11-30
10.7000

1684039121
050374500
2002
2017-02-01
3.2500

1684039321
050374500
2002
2017-05-02
3.3300

1684039371
050374500
2002
2017-07-07
3.8900

1684043441
050374500
2002
2017-01-03
4.3400

1684043395
067771960
2006
2018-01-01
12.5000

11727914496
067771960
2006
2017-11-30
10.7000

1684013435
050374500
2006
2017-02-01
3.2500

1684014435
050374500
2006
2017-05-02
3.3300

1684054435
050374500
2006
2017-07-07
3.8900

1684014215
050374500
2006
2017-01-03
4.3400


WITH cte AS 
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY item, location ORDER BY `date` DESC) rn
  FROM test
)
SELECT id, item, location, `date`, `value`
FROM cte
WHERE rn = 1;


id
item
location
date
value


1684039371
050374500
2002
2017-07-07
3.8900

1684054435
050374500
2006
2017-07-07
3.8900

16711663545
067771960
2002
2018-01-01
12.5000

1684043395
067771960
2006
2018-01-01
12.5000


SELECT DISTINCT
FIRST_VALUE(id) OVER (PARTITION BY item, location ORDER BY `date` DESC) id,
item,
location,
MAX(`date`) OVER (PARTITION BY item, location) `date`,
FIRST_VALUE(`value`) OVER (PARTITION BY item, location ORDER BY `date` DESC) `value`
FROM test;


id
item
location
date
value


1684039371
050374500
2002
2017-07-07
3.8900

1684054435
050374500
2006
2017-07-07
3.8900

16711663545
067771960
2002
2018-01-01
12.5000

1684043395
067771960
2006
2018-01-01
12.5000

ID	ITEM	LOCATION	DATE	VALUE
16711663545	067771960	2002	2018-01-01	12.5000
1684043432	067771960	2002	2017-11-30	10.7000
1684039121	050374500	2002	2017-02-01	3.2500
1684039321	050374500	2002	2017-05-02	3.3300
1684039371	050374500	2002	2017-07-07	3.8900
1684043441	050374500	2002	2017-01-03	4.3400
1684043395	067771960	2006	2018-01-01	12.5000
11727914496	067771960	2006	2017-11-30	10.7000
1684013435	050374500	2006	2017-02-01	3.2500
1684014435	050374500	2006	2017-05-02	3.3300
1684054435	050374500	2006	2017-07-07	3.8900
1684014215	050374500	2006	2017-01-03	4.3400