Grouping records from a table

Question

Please see the data in this file: https://drive.google.com/file/d/1gewGZst9zwEzntK9LnwZT4j86rHMVICS/view?usp=sharing

This data represents the recipe for a particular product on a particular date (the product being identified in column pmd_ppm_pr_code and the ingredients in pmd_pi_code). What I need to do is get the most recent recipe for a particular product. Here is the SQL I put together to do this;

select
    pmd_ppm_sio_reference
    ,pmd_ppm_pr_code
    ,pmd_ppm_date_effective
    ,pmd_percentage
    ,pmd_pi_code
    ,row_number()
    over(partition by pmd_ppm_pr_code, pmd_pi_code
        order by pmd_ppm_pr_code, pmd_pi_code, trunc(pmd_ppm_date_effective) desc) as "row"
from ppm_details
where pmd_ppm_sio_reference = 1801
and pmd_ppm_pr_code = 'A24052'

The where clause is just to restrict the results so I can see the wood from the trees.

Here are the results; https://drive.google.com/file/d/1mH7n0Q5yQYukJvgR7sh94OsdUaD8sXY_/view?usp=sharing

The issue is with the last item in the group where row = 1. And that's because that ingredient wasn't used in the latest recipe, but it has given me the latest entry where it was used.

I have tried adding the date into the partition, but all that does is give me row = 1 for every line.

Answer 1

There exists a column in your data that represents the recipe -- let me just randomly guess that this is pmd_ppm_pr_code. I assume the effective date is the most recent date. Let me assume that all products in the recipe have the same effective date.

If so, you want to use dense_rank(), not row_number():

dense_rank() over (partition by pmd_ppm_pr_code order by pmd_ppm_date_effective desc) as seqnum

Your version returns the most recent date for each product separately. This returns the most recent by recipe.

Note that your order by repeats the partitioning columns. This is totally unnecessary. The order by is within a partition.