Postgres 14

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

select version();

version

PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit

CREATE TABLE test(i int primary key, j int);

INSERT INTO test VALUES 
   (1,1),(2,1),(3,1),
   (4,2),(5,2),
   (6,3),(7,3),(8,3);
8 rows affected

SELECT min(i),j
FROM test
GROUP BY j
ORDER BY j DESC
LIMIT 2;

min
j

6
3

4
2

UPDATE test
SET j=5
WHERE j=1;
3 rows affected

SELECT min(i),j
FROM test
GROUP BY j
ORDER BY j DESC
LIMIT 2;

min
j

1
5

6
3

SELECT j
FROM TEST
GROUP BY j
ORDER BY MIN(i) DESC
LIMIT 2;

j

3

2