Postgresql how to do min and max with the name beside the value

Question

this is the data table

how to do to get below result:

Here is the result i want to get

I only can get lowest and highest without (name), any ideas to get above result?

Answer 1

Use GROUP BY for grouping the subjects and then fetch max_mark, min_mark using MAX(), MIN() functions respectively

    SELECT subject AS "sub1", 
    MAX(scc) AS "max_mark", 
    MIN(scc) AS "min_mark"
    FROM Subjects_mark
    GROUP BY 1

Now use subquery for formatting the output

SELECT sub1, 
CONCAT(min_mark,'(',studentnar,')') AS "lowest point",
CONCAT(max_mark,'(',studentnar,')') AS "highest point"
FROM Subjects_mark AS s1
JOIN (
        SELECT subjects AS "sub1", 
        MAX(scc) AS "max_mark", 
        MIN(scc) AS "min_mark"
        FROM Subjects_mark
        GROUP BY 1
    ) AS dt
ON dt.sub1 = s1.subjects 
AND dt.max_mark = s1.scc