SQL query for selecting records in a hierarchy-CodePudding

I am trying to write a query, to select records with the max number value [if duplicate exists for a order key combination], based on the following hierarchy:

If Order Key Combination has duplicates, and one of them has'30' code- We will select this record(having 30 code).
If Order Key Combination does not have '30' code and there are duplicates- We will select the record having max number.
If Order Key Combination does not have any duplicates- We will select that record as it is.

This is a sample input and output:

here

In my example,13 1 (order key combination) has 2 records, so we will pick up max of them.
14 1 also has 2 records, so we will pick up max of them.
15 1 has 2 records, but for one of them the code is 30- hence we will pick up that record, and will not consider max criteria for this order key.
16 1 has only 1 record, so we pick up that 1 record.

Please let me know on how can we solve this query. I am unable to think how shall I proceed.

CodePudding user response：

SELECT 
  a.order, 
  a.key, 
  case when b.code is NOT NULL then b.code ELSE a.code end as code, 
  case when b.number is NOT NULL then b.number ELSE max(a.number) end as number 
from 
  abc a 
  left join abc b on a.code = b.code 
  and b.code = 30 
GROUP by 
  a.order   a.key

CodePudding user response：

This solution uses 2 subqueries, first one finds records with max(number), second finds records with code = 30 and the largest number.

It will work correctly with multiple "code = 30" records and duplicate rows.

   select maxes.`order`, maxes.`key`, coalesce(thirties.`code`, maxes.`code`) as `code`, coalesce(thirties.`number`, maxes.`number`) as `number` 
    from
        (select distinct a.`order`, a.`key`, a.`code`, a.`number` from
            input a left join input b on a.`order` = b.`order` and a.`key` = b.`key` and a.number < b.number where b.order is null) as maxes
        left join
        (select `order`, `key`, `code`, max(number) as `number` from input where `code` = 30 group by `order`, `key`, `code`) as thirties
        on maxes.order = thirties.order and maxes.key = thirties.key
    order by maxes.`order`, maxes.`key`