The common way is to use ROW_NUMBER() within a CTE or subquery.

WITH CTE AS(

SELECT a.id,

a.first_name,

a.last_name,

b.income,

b.department,

ROW_NUMBER() OVER( PARTITION BY a.id ORDER BY b.income) rn

FROM CUSTOMERS a

INNER JOIN department b ON a.id = b.id

)

SELECT id,

first_name,

last_name,

income,

department

FROM CTE

WHERE rn = 1;

If you want ties, you could use RANK().

Feel free to ask any questions that you need to fully understand the code. 😉