SQLite 3.27

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

CREATE TABLE tablename (
  `id` INTEGER,
  `val` INTEGER,
  `parent` INTEGER,
  `letter` VARCHAR(1)
);
✓
INSERT INTO tablename
  (`id`, `val`, `parent`, `letter`)
VALUES
  ('1', '3', '10', 'a'),
  ('2', '3', '10', 'b'),
  ('3', '0', '10', 'c'),
  ('4', '5', '20', 'd');
✓
SELECT id, val, parent, letter
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY parent ORDER BY val DESC, id) rn
  FROM tablename
)
WHERE rn = 1


id
val
parent
letter


1
3
10
a

4
5
20
d


SELECT DISTINCT
       FIRST_VALUE(id) OVER (PARTITION BY parent ORDER BY val DESC, id) id, 
       MAX(val) OVER (PARTITION BY parent) val, 
       parent, 
       FIRST_VALUE(letter) OVER (PARTITION BY parent ORDER BY val DESC, id) letter
FROM tablename


id
val
parent
letter


1
3
10
a

4
5
20
d