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