Sql / Finding Min Max values for each elements of another column-CodePudding

Learning sql, as per title trying to print min and max values of a given column (sal) for each values in another (nodept). Using this query so far:

SELECT Nodept, sal FROM emp
WHERE sal IN ((SELECT MAX (sal) FROM emp), (SELECT MIN (sal) FROM emp))

Which works but only returns absolute min max and not min max for every entry in nodept. Documentation says it should be a simple matter of adding GROUP BY to the query but i fail to find the syntax for it.

Ty for the input.

CodePudding user response：

It sounds like you just want

select nodept, min(sal) min_sal, max(sal) max_sal
from   emp
group by nodept

If you want a row for each min and max per nodept then you want to add a filter to your subqueries:

SELECT Nodept, sal 
FROM   emp e1
WHERE e1.sal IN ((SELECT MAX (sal) FROM emp e2 where e2.nodept = e1.nodept)
                ,(SELECT MIN (sal) FROM emp e2 where e2.nodept = e1.nodept)
                )