DISTINCT in a WHERE Oracle

Asked

Viewed 32 times

0

I need to return the last two purchases of each table item NOTA_FISCAL_ENTRADA_ITEM, but returns column values VL_UNITARIO_ENTRADA_EST different.

That is, if in the last two purchases the value is the same, fetch the next most recent purchase with the different value.

In the SELECT below, I managed to return only the two most recent purchases. How do I bring the last two purchases with different values? I tried with the DISTINCT, but without success.

SELECT *
from    (
    SELECT CD_ITEM,
    DT_ENTRADA,
    VL_UNITARIO_ENTRADA_EST,
    row_number () over (partition by CD_ITEM order by DT_ENTRADA DESC) ULT_REG
    FROM NOTA_FISCAL_ENTRADA_ITEM
    )
WHERE ULT_REG <= 2

Current SELECT output:

exemplo

  • " That is, if in the last two purchases the value is the same, fetch the next most recent purchase with the different value" this is a little difficult to do without having an example of the data, but if using a group by including the value field, and a order by to ensure that you sort and take the next different value, you must solve

1 answer

0


Repurposing most of the steps taken. The idea here is to use the date field to remove duplicates, then at the same time we get the sort. On the outside, we keep only the first N entries (2).

SELECT * 
FROM (
  SELECT 
    CD_ITEM,
    VL_UNITARIO_ENTRADA_EST,
    DT_ENTRADA,
    row_number () over (partition by CD_ITEM order by DT_ENTRADA DESC) ULT_REG
  FROM (
    SELECT 
      CD_ITEM,
      VL_UNITARIO_ENTRADA_EST,
      MAX(DT_ENTRADA) AS DT_ENTRADA    
    FROM NOTA_FISCAL_ENTRADA_ITEM
    GROUP BY
      CD_ITEM,  
      VL_UNITARIO_ENTRADA_EST
    ORDER BY MAX(DT_ENTRADA) DESC
  ) NOTA_FISCAL_ENTRADA_ITEM_FILTERED
)
WHERE ULT_REG < 3;
  • Thanks so much @chegancasb !! It worked perfectly. That’s exactly what I needed!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.