SQL finding minimum that is non-zero

Question

I have issues finding the minimum values that are non-zero.

For example, I have a table that show the amount of money earned by each person, and some of them don't earn anything. I would like to exclude those that don't earn anything and find the names of people who earn the least (that is non-zero).

I tried this:

SELECT name, earnings
FROM paylist
WHERE earnings = (SELECT min(earnings)
            FROM paylist) AND EXISTS (SELECT * 
                                         FROM paylist
                                         WHERE earning <> 0)

But I realised that the min(earnings) will just become 0 and return no values in the output. How should I go about writing the query?

Also, I am told there could be two ways to write this: 1. with aggregate functions (e.g. COUNT, SUM, MAX, MIN) 2. without using any aggregate functions What's the difference?

Answer 1

Use the condition in the sub query to omit the rows with zero earnings and find minimum earning and use it outside.

select name,
    earnings
from paylist
where earnings = (
        select min(earnings)
        from paylist
        where earnings <> 0
        )

Or use nullif which can use the fact that min return minimum non-null value:

select name,
    earnings
from paylist
where earnings = (
        select min(nullif(earnings,0))
        from paylist
        )

The difference is that the first one can use index if any. The above two will return all the rows with minimum non-zero earnings

If you just want one row with least non-zero earnings, you can use limit (no aggregation needed):

select *
from paylist
where earnings <> 0
order by earnings
limit 1