Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (
    product int,
    plant text,
    store text,
    text text,
    week int,
    date text,
    stock int
);
    
insert into test 
values
(123456,'A123','Z12','HelloWorld',1,'2001-01-01',20),
(123456,'A123','Z12','HelloWorld',2,'2001-01-08',-15),
(123456,'A123','Z12','HelloWorld',3,'2001-01-16',-20),
(789123,'B345','123','HelloWorld1',1,'2001-01-01',10),
(789123,'B345','123','HelloWorld1',2,'2001-01-08',20),
(789123,'B345','123','HelloWorld1',3,'2001-01-16',30)
returning *;

CREATE TABLE




product
plant
store
text
week
date
stock


123456
A123
Z12
HelloWorld
1
2001-01-01
20

123456
A123
Z12
HelloWorld
2
2001-01-08
-15

123456
A123
Z12
HelloWorld
3
2001-01-16
-20

789123
B345
123
HelloWorld1
1
2001-01-01
10

789123
B345
123
HelloWorld1
2
2001-01-08
20

789123
B345
123
HelloWorld1
3
2001-01-16
30



INSERT 0 6


SELECT Product,Plant,Store,Text,Date, Stock FROM test
WHERE (Stock)<0 
order by Product,Plant,Store,Text,Date, Stock asc


product
plant
store
text
date
stock


123456
A123
Z12
HelloWorld
2001-01-08
-15

123456
A123
Z12
HelloWorld
2001-01-16
-20



SELECT 2


WITH added_row_number AS (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Product,Plant,Store ORDER BY Date ASC) AS row_number
  FROM test )
SELECT *
FROM added_row_number
WHERE (Stock)<0 AND row_number = 1;


product
plant
store
text
week
date
stock
row_number



SELECT 0


(select distinct on (product) * from test
order by product
         ,stock<0 desc
         ,date desc)
union
(select distinct on (product) * from test
where stock>=0
order by product
         ,date desc);


product
plant
store
text
week
date
stock


123456
A123
Z12
HelloWorld
3
2001-01-16
-20

123456
A123
Z12
HelloWorld
1
2001-01-01
20

789123
B345
123
HelloWorld1
3
2001-01-16
30



SELECT 3

product	plant	store	text	week	date	stock
123456	A123	Z12	HelloWorld	1	2001-01-01	20
123456	A123	Z12	HelloWorld	2	2001-01-08	-15
123456	A123	Z12	HelloWorld	3	2001-01-16	-20
789123	B345	123	HelloWorld1	1	2001-01-01	10
789123	B345	123	HelloWorld1	2	2001-01-08	20
789123	B345	123	HelloWorld1	3	2001-01-16	30