Select Only One Row, by Value Field, Ordering

Question

This Question extends from Oracle SQL Where Conditions weight but, is more general (without product restriction, but give one by product value).

I have a table

DROP TABLE mytable;
CREATE TABLE mytable 
(
    product_code VARCHAR2(20 BYTE) NOT NULL ENABLE, 
    priority NUMBER NOT NULL ENABLE, 
    date_act DATE, 
    date_dis DATE
);

Populate the table

INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('bla', '0', TO_DATE('2019-01-01', 'YYYY-MM-DD'), TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('bla', '1', TO_DATE('2019-02-01', 'YYYY-MM-DD'), TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('bla', '2', TO_DATE('2019-01-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('bla', '3', TO_DATE('2019-02-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('bla', '4', TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('bla', '5', TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('bla', '6');
INSERT INTO mytable (product_code, priority) VALUES ('bla', '7');

INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('foo', '0', TO_DATE('2019-01-01', 'YYYY-MM-DD'), TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('foo', '1', TO_DATE('2019-02-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('foo', '2', TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('foo', '3');


INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('tmp', '0', TO_DATE('2019-01-01', 'YYYY-MM-DD'), TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('tmp', '1', TO_DATE('2019-01-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('tmp', '2', TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('tmp', '3');

The content

SELECT * FROM mytable;

And Output

PRODUCT_CODE           PRIORITY DATE_ACT  DATE_DIS 
-------------------- ---------- --------- ---------
bla                           0 01-JAN-19 31-JAN-19
bla                           1 01-FEB-19 28-FEB-19
bla                           2 01-JAN-19          
bla                           3 01-FEB-19          
bla                           4           31-JAN-19
bla                           5           28-FEB-19
bla                           6                    
bla                           7                    
foo                           0 01-JAN-19 31-JAN-19
foo                           1 01-FEB-19          
foo                           2           31-JAN-19
foo                           3                    
tmp                           0 01-JAN-19 31-JAN-19
tmp                           1 01-JAN-19          
tmp                           2           28-FEB-19
tmp                           3

Condition definitions

condition_weight 4: When the row has date_act and date_dis defined.
condition_weight 3: When the row has date_dis defined but date_act not.
condition_weight 2: When the row has date_act defined but date_dis not.
condition_weight 1: When the row has date_act and date_dis not defined.

If condition_weight 4 is true the left (3, 2 and 1) will be ignored.

If condition_weight 4 is false and condition_weight 3 is true , condition_weight 2 and condition_weight 1 will be ignored.

If condition_weight 4 and 3 are false and condition_weight 2 is true condition_weight 1 will be ignored.

If condition_weight 4, 3 and 2 are false only condition_weight 1 will be evaluated.

THE QUESTION: How obtain only one row by each product_code with its priority highest, with input parameter date_submit with condition weight definitions?

EXAMPLE of OUTPUT Required NOTE: The below table results are created manually (I need similar results, but I don't have the query).

DATE_SUBMIT :='2019/01/15'

CONDITION_WEIGHT       PRIO PRODUCT_CODE           PRIORITY DATE_ACT  DATE_DIS  :DATE_SUBMIT                                                                                                                    
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
               4          0 bla                           0 01-JAN-19 31-JAN-19 2019/01/15                                                                                                                      
               4          0 foo                           0 01-JAN-19 31-JAN-19 2019/01/15                                                                                                                      
               4          0 tmp                           0 01-JAN-19 31-JAN-19 2019/01/15

DATE_SUBMIT :='2019/02/15'

CONDITION_WEIGHT       PRIO PRODUCT_CODE           PRIORITY DATE_ACT  DATE_DIS  :DATE_SUBMIT                                                                                                                    
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
               4          1 bla                           1 01-FEB-19 28-FEB-19 2019/02/15                                                                                                                      
               2          1 foo                           1 01-FEB-19           2019/02/15                                                                                                                      
               3          2 tmp                           2           28-FEB-19 2019/02/15

DATE_SUBMIT :='2018/12/31'

CONDITION_WEIGHT       PRIO PRODUCT_CODE           PRIORITY DATE_ACT  DATE_DIS  :DATE_SUBMIT                                                                                                                    
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
               3          5 bla                           5           28-FEB-19 2018/12/31                                                                                                                      
               3          2 foo                           2           31-JAN-19 2018/12/31                                                                                                                      
               3          2 tmp                           2           28-FEB-19 2018/12/31

DATE_SUBMIT :='2019/12/31'

CONDITION_WEIGHT       PRIO PRODUCT_CODE           PRIORITY DATE_ACT  DATE_DIS  :DATE_SUBMIT                                                                                                                    
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
               2          3 bla                           3 01-FEB-19           2019/12/31                                                                                                                      
               2          1 foo                           1 01-FEB-19           2019/12/31                                                                                                                      
               2          1 tmp                           1 01-JAN-19           2019/12/31

Check the answer https://stackoverflow.com/a/59779653/1410223 result, but if is it possible more simpler Query.

EDIT Only priority with highest priority must be .

. . Referring to another question is not going to help anyone understand the question that *you* have here. I would suggest that you explain the problem here. — Gordon Linoff, Jan 17 '20 at 15:53

kfinity · Answer 1 · 2020-01-17T16:30:05.583

I think this should do what you want.

select *
from (
    SELECT mytable.*,
        case when date_act is not null and date_dis is not null then 4
                 when date_dis is not null then 3
                 when date_act is not null then 2
                 else 1 end as weight,
        max(case when date_act is not null and date_dis is not null then 4
                 when date_dis is not null then 3
                 when date_act is not null then 2
                 else 1 end) over (partition by product_code) as max_weight
    FROM mytable
    where TO_DATE(:date_submit, 'yyyy/mm/dd') between coalesce(date_act, TO_DATE(:date_submit, 'yyyy/mm/dd'))
                        and coalesce(date_dis, TO_DATE(:date_submit, 'yyyy/mm/dd'))
    ) iv
where weight = max_weight
;

It would be a little simpler if you also wanted to filter by the :product_code parameter, like in your other question.

Sorry, the `priority` must be Keeping in mind. (I need only One row with the higher priority). — , Jan 17 '20 at 19:03

score 0 · Accepted Answer · answered Jan 17 '20 at 16:21

0

Assign condition weights using case when. Then take best row for each product, use rank():

with 
  d as (select date '2019-02-15' date_submit from dual),
  t as (
    select m.*, case 
                when date_act is not null and date_dis is not null then 4 
                when date_act is null and date_dis is not null then 3
                when date_act is not null and date_dis is null then 2
                else 1 end condition
      from mytable m)
select product_code, priority, date_act, date_dis, condition, date_submit 
  from (
    select t.*, d.*, 
           rank() over ( partition by product_code order by condition desc ) rnk
      from t join d 
        on date_submit between nvl(date_act, date_submit) and nvl(date_dis, date_submit))
  where rnk = 1

answered Jan 17 '20 at 16:21

Ponder Stibbons

14,723
2
21
24

Sorry, the `priority` must be Keeping in mind. (I need only One row with the higher priority). – Jan 17 '20 at 19:04
for works changing to `rank() over ( partition by product_code order by condition desc, priority desc) rnk` inserting --> `, priority desc` – Jan 17 '20 at 19:18
So add priority to `order by` as You did, but why is it `desc`? Shouldn't be priority ascending, 1 before 3? In that case use `order by condition desc, priority asc`. – Ponder Stibbons Jan 21 '20 at 10:48
I wan't to say (Hi Priority, Hi its value). – Jan 24 '20 at 17:35

Select Only One Row, by Value Field, Ordering

2 Answers2