Getting distinct values using tiered criteria

Question

I am trying to figure out how to take a table with with thousands of records and distill it down based on a few criteria. However the criteria are tiered. Its best explained by an example:

Product Key	Product Code	Qty Bundle	Product Group	Product Expiry
1	000ABC	1	Widgets	null
2	000ABC	5	Widgets	null
3	000ABC	1	Widgets	null
4	000ABC	10	Widgets	null
5	000DEF	1	Widgets	null
6	000DEF	10	Widgets	01/15/2021
7	000DEF	10	Widgets	null
8	000HIJ	5	Widgets	11/20/2020
9	000HIJ	10	Widgets	null
10	000HIJ	5	Widgets	null

Using the above as the example data, what I would want to end up with is a list where for each Product Code we are only returning a single Product Key. The criteria would be: if the product code has a Qty Bundle value = 1 then use that product key, if there are multiple Qty Bundles = 1 for a Product Code and both those Product Keys have not expired take the minimum Product Key, and if there are no Qty Bundle = 1 for a product code, simply take the minimum product key that is not expired.

Based on those criteria, the result of the script should yield:

Product Key	Product Code	Qty Bundle	Product Group	Product Expiry
1	000ABC	1	Widgets	null
5	000DEF	1	Widgets	null
9	000HIJ	10	Widgets	null

Any suggestions would be helpful!

Thanks!

Answer 1

You can use row_number() with appropriate ordering criteria:

select t.*
from (select t.*,
             row_number() over (partition by productcode
                                order by (case when qty_bundle = 1 and expiry is null then 1
                                               else 2
                                          end),
                                         (case when expiry is not null then 1 else 2
                                               else 3
                                          end),
                                         productkey
                               ) as seqnum
      from t
     ) t
where seqnum = 1;