Group by with having clause a key not equal

Question

I have a table structure and values as:-

used_items

id  item_name   qty

24  Potatoes    0
25  Potatoes    500
26  test        88
27  Wheat       3
28  abc         10

I want to group according to item_name such as query should return single item_name where qty!=0. I have used this query-

SELECT * from used_items group by item_name having qty!='0'

This query doesn't return Potatoes, the result-

  id    item_name   qty

    26  test        88
    27  Wheat       3
    28  abc         10

Expected Result-

id  item_name   qty

25  Potatoes    500
26  test        88
27  Wheat       3
28  abc         10

It should also return the item_name Potatoes with non zero value including in the group. How can I achieve this?

Edits- I have used Group by because i want unique item_name and it should skip the item_name if qty is 0 for all same item_name. But the query skips if atleast one of the row qty is 0(such as Potatoes). Also, if qty is 0, it should get the row which matches the item_name according to the id ascending. Hope i am clear, for eg;

The data:-

id  item_name   qty

24  Potatoes    0
25  Potatoes    500
27  Potatoes    400
28  test        88
29  Wheat       3
30  abc         10

Expected result, returning unique item_name with first inserted non-zero value

id  item_name   qty
25  Potatoes    500
28  test        88
29  Wheat       3
30  abc         10

Answer 1

If you want for each item_name the 1st non 0 (for the quantity) row then you can do it with a correlated subquery:

SELECT t.*
FROM tablename t
WHERE t.id = (SELECT MIN(id) FROM tablename WHERE item_name = t.item_name AND qty <> 0)

See the demo.
Results:

> id | item_name | qty
> -: | :-------- | --:
> 25 | Potatoes  | 500
> 28 | test      |  88
> 29 | Wheat     |   3
> 30 | abc       |  10