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?

How do you tell which is the oldest if you don't have any date column? What you wrote above sounds more like LIFO then FIFO. — XraySensei, Jun 18 '23 at 06:25
The query you post doesn't give the output you show, it only produces two rows, not three. See https://dbfiddle.uk/zP17jn8Y — Mark Rotteveel, Jun 18 '23 at 08:37
Thank you XRaySensei , LOTNO shows whichone is older for example 001 the oldest 002 new 003 the newest etc. — Henry Olive, Jun 18 '23 at 08:42

score 0 · Answer 1 · answered Jun 18 '23 at 08:42

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

How to return the first row after group by?

1 Answers1