Postgres 11

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

CREATE TABLE t(id INT, foo int, bar int);
INSERT INTO t VALUES
(1, 10, 20),
(1, 11, 21),
(1, 12, 22),
(2, 30, 40),
(2, 31, 41),
(2, 32, 42);
6 rows affected

SELECT
    DISTINCT ON (id) id,
    foo,
    bar
FROM
    t
ORDER BY 
    id, foo DESC;


id
foo
bar


1
12
22

2
32
42


SELECT
    DISTINCT ON (id) id,
    foo,
    MAX(bar) bar
FROM
    t
ORDER BY 
    id, foo;
ERROR:  column "t.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:     DISTINCT ON (id) id,
                             ^


SELECT
    DISTINCT ON (id) id,
    foo,
    (SELECT 
        MAX(bar)
     FROM
       t t1
     WHERE
       t1.id = t.id) max_bar
FROM
    t
ORDER BY 
    id, max_bar DESC;


id
foo
max_bar


1
10
22

2
30
42


SELECT
    DISTINCT ON (id) id,
    foo,
    bar
FROM
    t
ORDER BY 
    id, bar DESC;


id
foo
bar


1
12
22

2
32
42