How to return the first row after group by?

Question

My table looks like the following:

ID	ITEMNO	LOCATIONNO	LOTNO	INCOME	OUTGO
1	AAA	A-01	001	1	0
2	AAA	A-02	002	1	0
3	AAA	A-01	001	0	1

When a user sells an item, I want to show the oldest income LOTNO item (FIFO) to the user (lookup) and the user must choose the oldest LOTNO item. In short, I want to get the following result:

ITEMNO	LOCATIONNO	LOTNO	STOCK
AAA	A-02	002	1

I tried the following SQL:

SELECT ITEMNO, LOCATIONNO, LOTNO, SUM(INCOME-OUTGO) STOCK
FROM LOCATION_DETAIL 
WHERE ITEMNO = 'AAA'
GROUP BY ITEMNO, LOCATIONNO, LOTNO
ORDER BY LOTNO

The result of the above SQL is like the following:

ITEMNO	LOCATIONNO	LOTNO	STOCK
AAA	A-01	001	1
AAA	A-02	002	1
AAA	A-01	001	-1

What am I doing wrong?

Answer 1

After the group by, you need to filter the query using HAVING to only include positive stock, order by lotno, and only return the first row using the FETCH clause.

So:

select ITEMNO, LOCATIONNO, LOTNO, sum(INCOME-OUTGO) as STOCK
from LOCATION_DETAIL
where ITEMNO = 'AAA'
group by ITEMNO, LOCATIONNO, LOTNO
having sum(INCOME-OUTGO) > 0
order by LOTNO
fetch first row only

See also this dbfiddle: https://dbfiddle.uk/ve3WZ73u