SQLite 3.27

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

CREATE TABLE tablename (C, G, H, I, J);
INSERT INTO tablename VALUES
  ('c1', '2022-06-01', 1, 20, 3),
  ('c1', '2022-06-02', 10, 2, 3),
  ('c1', '2022-06-03', 1, 2, 3),
  
  ('c2', '2022-06-04', 10, 20, 30),
  ('c2', '2022-06-05', 100, 2, 3),
  ('c2', '2022-06-06', 1, 2, 3);
WITH cte AS (
  SELECT C, MAX(MAX(H), MAX(I), MAX(J)) max
  FROM tablename
  GROUP BY C
)
SELECT t.C, t.G 
FROM tablename t
WHERE (t.c, MAX(t.H, t.I, t.J)) IN (SELECT C, max FROM cte);


C
G


c1
2022-06-01

c2
2022-06-05


WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY C ORDER BY MAX(H, I, J) DESC) rn
  FROM tablename
)
SELECT C, G 
FROM cte
WHERE rn = 1;


C
G


c1
2022-06-01

c2
2022-06-05


SELECT DISTINCT C, 
       FIRST_VALUE(G) OVER (PARTITION BY C ORDER BY MAX(H, I, J) DESC) G
FROM tablename;


C
G


c1
2022-06-01

c2
2022-06-05