Question 254612 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get only the first row when exist a negative value else get the last positive value

I have the next 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

So I would like to get the row for the first negative value but if is not negative values then get the last positive value. Also restructured the table like this.

Product Plant Store Text Date_Start Stock_Start Date_Finished Stock_Finished
123456 A123 Z12 HelloWorld 2001-01-01 20 2001-01-08 -15
789123 B345 123 HelloWorld1 2001-01-01 10 2001-01-16 30

I try multiple query’s but the one with the nearest result is as follows:

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

Also I tried to use FETCH FIRST 1 ROWS ONLY but I got the next error ERROR: Syntax error at or near "FIRST".
The other query is as follows:

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

But beware the data and if I add a case stament I got the error ERROR: Syntax error at or near "CASE" . Any help?

Tags:

2

Answers


  1. 0

    Some Subselects can be used, to solve this

    SELECT "Product", "Plant", "Store"
  , (SELECT "Date" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store"  = t2."Store" ORDER BY "Week" ASC  LIMIT 1) start_date
  
  , (SELECT "Stock" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store"  = t2."Store"  ORDER BY "Week" ASC LIMIT 1) start_stock
    , (SELECT "Date" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store" = t2."Store" ORDER BY "Week" DESC LIMIT 1) en_date
  
  , (SELECT "Stock" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store" = t2."Store"  ORDER BY "Week" DESC LIMIT 1) end_stock
  FROM
 Table2 t1
  GROUP BY 
    "Product", "Plant", "Store"
    Product Plant Store start_date start_stock en_date end_stock
    789123 B345 123 2001-01-01 10 2001-01-16 30
    123456 A123 Z12 2001-01-01 20 2001-01-16 -20 
    SELECT 2

    fiddle

    you don’t need for this aggregation, i can’t find a Version postgres that doesn’t work

    SELECT DISTINCT "Product", "Plant", "Store"
  , (SELECT "Date" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store"  = t2."Store" ORDER BY "Week" ASC  LIMIT 1) start_date
  
  , (SELECT "Stock" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store"  = t2."Store"  ORDER BY "Week" ASC LIMIT 1) start_stock
    , (SELECT "Date" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store" = t2."Store" ORDER BY "Week" DESC LIMIT 1) en_date
  
  , (SELECT "Stock" FROM Table2 t2 WHERE t1."Product" = t2."Product" AND  t1."Plant" = t2."Plant"
  AND t1."Store" = t2."Store"  ORDER BY "Week" DESC LIMIT 1) end_stock
  FROM
 Table2 t1
    Product Plant Store start_date start_stock en_date end_stock
    123456 A123 Z12 2001-01-01 20 2001-01-16 -20
    789123 B345 123 2001-01-01 10 2001-01-16 30 
    SELECT 2

    fiddle

    Login or Signup to reply.
  2. 0

    I need the first stock’s negative value with its date and also the
    first stock’s value with its date in order to calculate the difference
    between both dates, but if it’s not a negative in the stock then get
    me the last value with its day

    You could rephrase this as "latest positive stock per product, also latest negative per product", which implies

    • if there’s no positive stock for a product, you get only negative stock records,
    • if there’s no negative stock for a product, you get only positive stock records.

    As mentioned in the comment, usually the 1 per product translates into a distinct on: demo

    (select distinct on (product) * from test
order by product
         ,case stock<0 
              when true then 0 
              else 1 end
         ,date desc)
union all
select distinct on (product) * from test
where product in (select product from test where stock<0)
and stock>0
order by date desc;
    product plant store text week date stock
    123456 A123 Z12 HelloWorld 3 2001-01-16 -20
    789123 B345 123 HelloWorld1 3 2001-01-16 30
    123456 A123 Z12 HelloWorld 1 2001-01-01 20
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search