How to show a column in a 'max function' without putting this column in a 'group by&#-CodePudding

I'm struggling to figure this out... What I am missing?

I have 3 tables that connects to each other:

Invoice has 'id' and 'entry date' Invoice Item has 'id' that connects to 'invoice' and product that connects to 'Product' Product has 'product cod' that connects to 'invoice item'

I need to select the last entry date of all products in the inventory

My select is seen below:

SELECT Max(ENTRY)
    , PRODUCT
    , NAME
     
    FROM ( 
       SELECT TO_DATE(NT.ENTRY, 'dd/mm/yyyy') "ENTRY"
       , NT.INVOICE_ID     "INVOICE"
       , NTI.PRODUCT  "PRODUCT"
       , P.NAME       "NAME"
       FROM INVOICE NT 
       INNER JOIN INVOICE_ITEM NTI ON NTI.INVOICE_ID = NT.INVOICE_ID
       INNER JOIN PRODUCTS P ON P.COD = NTI.PRODUCT
       ORDER BY ENTRY ASC)  
    GROUP BY PRODUCT, NAME

The results are fine by this:

MAX(ENTRY)       PRODUCT NAME
03/11/021 00:00:00  3   AGULHA
03/11/021 00:00:00  11  MOUSE
30/04/021 00:00:00  8   LUVAS DESCARTAVEIS
20/11/019 00:00:00  6   FLAGYL
30/04/020 00:00:00  4   DORFLEX
30/04/022 00:00:00  1   CABO 25MM
05/04/020 00:00:00  7   MASCARA PFF2
20/02/020 00:00:00  2   SERINGA

Now I should add Invoice_ID column connected to the 'max entry'

If I add NT.INVOICE_ID in the top select, an error appear saying I should include "INVOICE_ID" in the "group by" function. But if I do add "INVOICE_ID" in the group by, my select breaks into more rows than the ideal result.

SELECT Max(ENTRY)
    , PRODUCT
    , NAME
    , INVOICE 
    FROM ( 
       SELECT TO_DATE(NT.ENTRY, 'dd/mm/yyyy') "ENTRY"
       , NT.INVOICE_ID     "INVOICE"
       , NTI.PRODUCT  "PRODUCT"
       , P.NAME       "NAME"
       FROM INVOICE NT 
       INNER JOIN INVOICE_ITEM NTI ON NTI.INVOICE_ID = NT.INVOICE_ID
       INNER JOIN PRODUCTS P ON P.COD = NTI.PRODUCT
       ORDER BY ENTRY ASC)  
    GROUP BY PRODUCT, NAME, INVOICE

Results into:


MAX(ENTRY)      PRODUCT   NAME    INVOICE
20/11/019 00:00:00  1   CABO 25MM   3264
30/04/015 00:00:00  2   SERINGA 333
03/11/021 00:00:00  11  MOUSE   100
20/02/020 00:00:00  7   MASCARA PFF2    4600
30/04/018 00:00:00  4   DORFLEX 101
30/04/022 00:00:00  1   CABO 25MM   345
03/11/021 00:00:00  3   AGULHA  100
03/11/021 00:00:00  1   CABO 25MM   100
30/04/018 00:00:00  2   SERINGA 101
30/04/015 00:00:00  1   CABO 25MM   333
30/04/020 00:00:00  4   DORFLEX 102
20/11/019 00:00:00  3   AGULHA  3264
20/02/020 00:00:00  2   SERINGA 4600
30/04/021 00:00:00  8   LUVAS DESCARTAVEIS  402
20/11/019 00:00:00  6   FLAGYL  3264
13/07/017 00:00:00  6   FLAGYL  1644
30/04/018 00:00:00  8   LUVAS DESCARTAVEIS  101
30/04/015 00:00:00  2   SERINGA 400
05/04/020 00:00:00  7   MASCARA PFF2    125
12/06/017 00:00:00  3   AGULHA  127

Invoice_ID should be connect to the last entry. MAX(Invoice) will not show the correct select because invoice has several low and high numbers. Is there any way to solve it please?

CodePudding user response：

You could use a join bettwen you result and invoice

select t1.max_entry, t2.invoice
from  (
    SELECT Max(ENTRY) max_entry
        , PRODUCT
        , NAME
         
        FROM ( 
           SELECT TO_DATE(NT.ENTRY, 'dd/mm/yyyy') "ENTRY"
           , NT.INVOICE_ID     "INVOICE"
           , NTI.PRODUCT  "PRODUCT"
           , P.NAME       "NAME"
           FROM INVOICE NT 
           INNER JOIN INVOICE_ITEM NTI ON NTI.INVOICE_ID = NT.INVOICE_ID
           INNER JOIN PRODUCTS P ON P.COD = NTI.PRODUCT
           ORDER BY ENTRY ASC)  
        GROUP BY PRODUCT, NAME
) t1 
inner join INVOICE t2 on t1.max_entry = t2.entry

CodePudding user response：

While Oracle offers its propriatary KEEP LAST for this, this is a task that is usually solved with analytic functions like MAX|ROW_NUMBER|RANK| ... OVER.

SELECT invoice_date, invoice_id, product_code, product_name
FROM
(
  SELECT 
    TO_DATE(nt.entry, 'dd/mm/yyyy') AS invoice_date,
    MAX(TO_DATE(nt.entry, 'dd/mm/yyyy')) OVER (PARTITION BY nti.product) AS max_invoice_date_of_product,
    nt.invoice_id
    nti.product AS product_code,
    p.name AS product_name
  FROM invoice nt 
  INNER JOIN invoice_item nti ON nti.invoice_id = nt.invoice_id
  INNER JOIN products p ON p.cod = nti.product
)
WHERE invoice_date = max_invoice_date_of_product
ORDER BY product_code;

(And you probably know that it is a bad idea to store dates as strings in a database.)