MySQL 8.0

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

CREATE TABLE country (
  country_id     INT,
  country_code   VARCHAR(8),
  total_users    INT
);

INSERT INTO
  country
VALUES
  (2,	'FR',	10),
  (10,	'US',	100),
  (27,	'UK',	200),
  (400,	'EU',	160),
  (500, 'GLOBAL', 150)
;


Records: 5  Duplicates: 0  Warnings: 0


CREATE TABLE product_price (
  product_id   VARCHAR(8),
  price_id     VARCHAR(2),
  price        DECIMAL(18,6),
  country_id   VARCHAR(8)
);

INSERT INTO
  product_price
VALUES
  ('product1', 	'1a', 	19.99, 	27), 
  ('product1', 	'1b', 	20.99, 	400), 
  ('product1', 	'1c', 	30.99, 	500), 
  ('product2', 	'2a', 	199.99,	10), 
  ('product2', 	'2b', 	299.99,	400), 
  ('product3', 	'3a', 	50.99, 	500), 
  ('product4', 	'4a', 	40, 	2), 
  ('product4', 	'4a', 	45, 	10) 
;

Records: 8  Duplicates: 0  Warnings: 0


SELECT
  *
FROM
(
  SELECT
    pp.product_id,
    pp.price_id,
    pp.price,
    pp.country_id as country,
    ROW_NUMBER() OVER (
      PARTITION BY pp.product_id
          ORDER BY FIELD(
                     c.country_id,
                     27,
                     400,
                     500,
                     c.country_id
                   ),
                   c.total_users DESC
    )
      AS rowNumber
  FROM
    product_price   pp
  INNER JOIN
    country         c
      ON pp.country_id = c.country_id
)
  AS ranked_price
WHERE
  rowNumber=1;


product_id
price_id
price
country
rowNumber


product1
1a
19.990000
27
1

product2
2b
299.990000
400
1

product3
3a
50.990000
500
1

product4
4a
45.000000
10
1

product_id	price_id	price	country	rowNumber
product1	1a	19.990000	27	1
product2	2b	299.990000	400	1
product3	3a	50.990000	500	1
product4	4a	45.000000	10	1