CODEWITHSUNDEEP
sqloracle-database
1pluszara
1pluszara

Reputation: 1528

Oracle: Pick the row based on a given date

I have below data:

WITH  T AS (
SELECT 333 "ID",TO_DATE('2015/04/01','yyyy/mm/dd') "DT",331 AS VAL1,'GG' AS VAL2 FROM DUAL
UNION ALL 
SELECT 444 "ID",TO_DATE('2012/05/10','yyyy/mm/dd') "DT",441 AS VAL1,'AA' AS VAL2 FROM DUAL
UNION ALL 
SELECT 444 "ID",TO_DATE('2013/03/01','yyyy/mm/dd') "DT",442 AS VAL1,'AA1' AS VAL2 FROM DUAL
UNION ALL 
SELECT 444 "ID",TO_DATE('2018/08/12','yyyy/mm/dd') "DT",443 AS VAL1,'AA1' AS VAL2 FROM DUAL
UNION ALL
SELECT 555 "ID",TO_DATE('2012/05/10','yyyy/mm/dd') "DT",551 AS VAL1,'AA' AS VAL2 FROM DUAL
UNION ALL 
SELECT 555 "ID",TO_DATE('2017/03/01','yyyy/mm/dd') "DT",552 AS VAL1,'BB1' AS VAL2 FROM DUAL
UNION ALL 
SELECT 555 "ID",TO_DATE('2018/03/01','yyyy/mm/dd') "DT",553 AS VAL1,'GGGA1' AS VAL2 FROM DUAL
UNION ALL
SELECT 555 "ID",TO_DATE('2018/09/12','yyyy/mm/dd') "DT",554 AS VAL1,'JJBB1' AS VAL2 FROM DUAL
UNION ALL 
SELECT 555 "ID",TO_DATE('2019/10/09','yyyy/mm/dd') "DT",555 AS VAL1,'OOOUA1' AS VAL2 FROM DUAL
UNION ALL 
SELECT 666 "ID",TO_DATE('2012/05/10','yyyy/mm/dd') "DT",441 AS VAL1,'AA' AS VAL2 
FROM DUAL
UNION ALL 
SELECT 666 "ID",TO_DATE('2013/03/01','yyyy/mm/dd') "DT",442 AS VAL1,'AA1' AS VAL2 
FROM DUAL
UNION ALL 
SELECT 666 "ID",TO_DATE('2016/08/12','yyyy/mm/dd') "DT",443 AS VAL1,'AA1' AS VAL2 
FROM DUAL
)
SELECT * FROM (
SELECT id,val1 ,val2,dt
      ,ROW_NUMBER() OVER(PARTITION BY id ORDER BY dt ASC) rnk
  FROM T
);

        ID       VAL1 VAL2   DT               RNK
---------- ---------- ------ --------- ----------
       333        331 GG     01-APR-15          1
       444        441 AA     10-MAY-12          1
       444        442 AA1    01-MAR-13          2
       444        443 AA1    12-AUG-18          3
       555        551 AA     10-MAY-12          1
       555        552 BB1    01-MAR-17          2
       555        553 GGGA1  01-MAR-18          3
       555        554 JJBB1  12-SEP-18          4
       555        555 OOOUA1 09-OCT-19          5

Here, I need to pick the record based on the input date.

Ex: INPUT_DATE = '17-DEC-2016'

Scenarios:

  1. If an ID has only only one record, pick that one record -Simple
  2. If an ID has mulitple records pick the record where "INPUT_DATE" greater than the "DT" column and then pick the MINIMUM Date

So expected Output:

        ID       VAL1 VAL2   DT               RNK
---------- ---------- ------ --------- ----------
       333        331 GG     01-APR-15          1
       444        443 AA1    12-AUG-18          3
       555        552 BB1    01-MAR-17          2

Upvotes: 1

Views: 61

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You would do:

select t.*
from (select t.*,
             row_number() over (partition by id,
                                order by (case when dt >= INPUT_DT then 1 else 2 end), dt asc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 2

FXD
FXD

Reputation: 2060

The following queries should work for both your scenarios.

Version that returns 2 records for ids that have non-unique dt.

SELECT * FROM t MyT
WHERE dt = (
    SELECT MIN(DT) FROM t
    WHERE id = t.id AND dt >= INPUT_DATE
)

Version that will return always 1 record (the record it chooses to keep is "random")

SELECT *
FROM (
    SELECT *, row_number() OVER (PARTITION BY id ORDER BY dt) AS MyRank FROM T
    WHERE dt >= INPUT_DATE
) MyT
WHERE MyRank = 1

You can also get the same result as the 1st query by taking the 2nd one and changing row_number() into rank(). It's really about the syntax you prefer at that point.

Upvotes: 1

Jo Joseph
Jo Joseph

Reputation: 59

I dont have oracle database so this is not exact code but you can try something like below.

 select * from test where val1 in(
 select val1 from test  where dt in 
 (select max(DT) from test where DT>INPUT_DATE group by val1))

Upvotes: 0

Related Questions