How to “fetch first 1 row only” per IDs in 3 different columns?

I am attempting to fetch 1 row only based on unique set of IDs in another 3 columns. In my database, there are many records per date for each of a unique set of IDs in the other 3 columns (you can imagine product numbers for instance). The stock status is issued only per date (change), not as one final number of a quantity. Therefore the only way to get actual stock status is to check the latest stock status update with the latest date – most top row always per a given combination of product IDs.

This is how my table looks like at the moment:

Table with raw data

Code1         Cde2         Code3           Date        Stock status
arti-cd-base  arti-cd-sfx  adfc-cd-diffco  stof-dt     stof-qty-op
------------  -----------  --------------  ----------  -----------
      1            15            0         2019-08-31       200
      1            15            0         2019-08-25       290
      2            16            2         2019-08-28       100
      2            16            2         2019-08-26        80
      2            16            2         2019-08-21       200
      3            18           25         2019-08-18        75

And this is how I wish it would looks like – visible only the rows with the latest date (stpf-dt) and stock status (stof-qty-op) per each combination of arti-cd-base, arti-cd-sfx and adfc-cd-diffco.

Table should look like this

Code1         Cde2         Code3           Date        Stock status
arti-cd-base  arti-cd-sfx  adfc-cd-diffco  stof-dt     stof-qty-op
------------  -----------  --------------  ----------  -----------
      1            15            0         2019-08-31       200
      2            16            2         2019-08-28       100
      3            18           25         2019-08-18        75

The top column IDs are consecutively as follows:

      Code1         Code2          Code3          Date  -   Stock status
│ arti-cd-base │ arti-cd-sfx │ adfc-cd-diffco │   stof-dt   │ stof-qty-op │

Is there any possible way via SQL to achieve this? I found an option to display one row only via the command: “offset 0 row fetch first 1 row only”, however this displays simply 1 row, but does not respect one row per a set of product IDs given in the other three columns (arti-cd-base, arti-cd-sfx and adfc-cd-diffco). Would anyone see any way through?

Answer

It looks like you want to keep the first value of the last column in an aggregation. Oracle offers this functionality, using keep:

select "arti-cd-base", "arti-cd-sfx", "adfc-cd-diffco",  
       max("stof-dt") as "stof-dt",
       max("stof-qty-op") keep (dense_rank first order by "stof-dt" desc) as "stof-qty-op"
from t
group by "arti-cd-base", "arti-cd-sfx", "adfc-cd-diffco";

Advertisement

Answer