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

Question

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:

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.

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 1

Check out this option:

SELECT *
FROM (
    SELECT * 
    ,   ROW_NUMBER() OVER ( PARTITION BY arti_cd_base ORDER BY stof_dt DESC) AS rwn
    FROM yourTable
    ) x
WHERE x.rwn = 1 

Or in your case :

    SELECT *
    FROM ( 
        SELECT stof_0."arti-cd-base"
        , stof_0."arti-cd-sfx"
        , stof_0."adfc-cd-diffco"
        , stof_0."dcmf-nr"
        , stof_0."stof-dt"
        , stof_0."stof-qty-op"
        , stof_0."stof-qty-nop"
        , stof_0."stof-qty-brok"
        , stof_0."stof-qty-brok-ok"
        , stof_0."stof-qty-ext"
        , stof_0."stof-qty-dock"
        , stof_0."stof-qty-trans"
        , stof_0."stof-qty-diff"
        , stof_0."stof-qty-fin-qty"
        , stof_0."stof-qty-fin-temp"
        , stof_0."stof-am" 
        , ROW_NUMBER() OVER (PARTITION BY stof_0."arti-cd-base" ORDER BY stof_0."stof-dt" DESC ) AS rwn
        FROM NILOS.PUB.stof stof_0 
        WHERE (stof_0."arti-cd-base"=1) 
        AND (stof_0."arti-cd-sfx"=15) 
        AND (stof_0."adfc-cd-diffco"=0) 
        ) x
    WHERE x.rwn = 1 

Or:

SELECT x.*
FROM (
        SELECT stof_0."arti-cd-base"
        , stof_0."arti-cd-sfx"
        , stof_0."adfc-cd-diffco"
        , stof_0."dcmf-nr"
        , stof_0."stof-dt"
        , stof_0."stof-qty-op"
        , stof_0."stof-qty-nop"
        , stof_0."stof-qty-brok"
        , stof_0."stof-qty-brok-ok"
        , stof_0."stof-qty-ext"
        , stof_0."stof-qty-dock"
        , stof_0."stof-qty-trans"
        , stof_0."stof-qty-diff"
        , stof_0."stof-qty-fin-qty"
        , stof_0."stof-qty-fin-temp"
        , stof_0."stof-am" 
        FROM NILOS.PUB.stof stof_0 
        WHERE (stof_0."arti-cd-base"=1) 
        AND (stof_0."arti-cd-sfx"=15) 
        AND (stof_0."adfc-cd-diffco"=0) 
    ) x
INNER JOIN ( SELECT stof_1."arti-cd-base"
            ,   MAX(stof_1."stof-dt") AS max_stof_dt
         FROM NILOS.PUB.stof stof_1 
         GROUP BY stof_1."arti-cd-base" ) y ON x"arti-cd-base" = y."arti-cd-base"
                                            AND x."stof-dt" = y."max_stof_dt"

This returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. So in your case partition is arti_cd_base, and we are ordering by the oldest date per partition. Oldest date per each partition will have always have number 1, thats why there is condition that result of this function must equal 1.

Answer 2

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";