select newest record but also ignore records that contain completed-CodePudding

I have to join three tables:

date: productorder, status, date
product: productorder, productID
info : productID, info

I want to group all the productorder by newest record from date, but also ignore productorder with status 'completed'.

here is what i have:

SELECT date.productorder, date.status, info.info <br>
FROM (SELECT productorder, MAX(date) from date where status <> 'completed') newest <br>
INNER JOIN product <br>
ON product.productorder = newest.productorder <br>
INNER JOIN info <br>
ON info.productID = product.ProductID;

This return too many results so I know it is not correct. How can I get max value but at the same time ignore the completed productorder?

CodePudding user response：

Table and column name most probably isn't date; that's a reserved word (for datatype name).

Anyway: you could filter out rows by status, rank the rest by date and then join the result to other tables, fetching only the highest ranking ones.

with t_date as
  (-- rank rows whose status isn't "completed" by date value, partitioned by each productorder
   select productorder, status, datum,
          rank() over (partition by productorder order by datum desc) rnk
   from datum
   where status <> 'completed'
  )
select d.productorder, 
       d.status, 
       i.info
from t_date d join product p on p.productorder = d.productorder
              join info i on i.productid = p.productid
where d.rnk = 1

CodePudding user response：

Assuming that a product order can have multiple statuses in its lifetime and you want to ignore the entire product order when it reaches a "completed" status then you do not want to filter rows using <> 'completed' as you will still be left with the earlier rows from those completed orders in the history. Instead you can use conditional aggregation in a HAVING clause to check if the order has any completed statuses:

SELECT n.productorder,
       n.date_column,
       n.status,
       i.info
FROM (
       SELECT productorder,
              MAX(date_column) AS date_column,
              MAX(status) KEEP (DENSE_RANK LAST ORDER BY date_column) AS status
       FROM   date_table
       HAVING COUNT(CASE status WHEN 'completed' THEN 1 END) = 0
     ) n
     INNER JOIN product p
     ON p.productorder = n.productorder
     INNER JOIN info i
     ON i.productID = p.ProductID;

Note: DATE is a reserved word and cannot be used as an (unquoted) identifier (and it is bad practice to use quoted identifiers and worse practice to use reserved words in quoted identifiers).