Postgres 15

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

CREATE TABLE mytable (
  product varchar(25), 
  Version int,
  Results int
);

insert into mytable values
('X', 1, 50),
('X', 1, 51),
('X', 1, 53),
('X', 2, 60),
('X', 2, 62),
('X', 3, 70),
('X', 4, 80),
('X', 4, 80),
('X', 4, 80),
('Y', 1, 50),
('Y', 2, 60),
('Y', 3, 70),
('Y', 4, 80);

CREATE TABLE



INSERT 0 13


select *, row_number() over (partition by product, Version order by Results desc) as rn
from mytable


product
version
results
rn


X
1
53
1

X
1
51
2

X
1
50
3

X
2
62
1

X
2
60
2

X
3
70
1

X
4
80
1

X
4
80
2

X
4
80
3

Y
1
50
1

Y
2
60
1

Y
3
70
1

Y
4
80
1



SELECT 13


with cte as (
  select *, row_number() over (partition by product, Version order by Results desc) as rn
  from mytable
)
select product, Version, Results
from cte
where rn = 1;


product
version
results


X
1
53

X
2
62

X
3
70

X
4
80

Y
1
50

Y
2
60

Y
3
70

Y
4
80



SELECT 8

product	version	results	rn
X	1	53	1
X	1	51	2
X	1	50	3
X	2	62	1
X	2	60	2
X	3	70	1
X	4	80	1
X	4	80	2
X	4	80	3
Y	1	50	1
Y	2	60	1
Y	3	70	1
Y	4	80	1

product	version	results	rn
X	1	53	1
X	1	51	2
X	1	50	3
X	2	62	1
X	2	60	2
X	3	70	1
X	4	80	1
X	4	80	2
X	4	80	3
Y	1	50	1
Y	2	60	1
Y	3	70	1
Y	4	80	1

product	version	results	rn
X	1	53	1
X	1	51	2
X	1	50	3
X	2	62	1
X	2	60	2
X	3	70	1
X	4	80	1
X	4	80	2
X	4	80	3
Y	1	50	1
Y	2	60	1
Y	3	70	1
Y	4	80	1