4

I have table like below

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  1 | PHYSICAL | Y   | 10-Oct      |   8403 |
|  1 | BILL     | Y   | 6-Oct       |    135 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 8-Oct       |   3673 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  57923 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+

need to check below multiple conditions before populating the data

  1. If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
  2. If record has a combination of duplicate id, addr, Ind and update_Date, select only one record from the duplicate records (It can be any record).

Below was the expected result.

Can anyone plug me some ideas to achieve this.

here is the sqlfiddle: http://sqlfiddle.com/#!4/d0098/1

+----+----------+-----+-------------+--------+
| ID |   ADDR   | IND | Update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
|  1 | PHYSICAL | Y   | 11-Oct      |   1293 |
|  1 | BILL     | Y   | 10-Oct      |   1357 |
|  1 | BILL     | N   | 10-Sep      |  13567 |
|  2 | PHYSICAL | Y   | 9-Oct       |   5333 |
|  2 | BILL     | N   | 8-Aug       |    657 |
|  2 | BILL     | Y   | 10-Oct      |   2574 |
|  3 | BILL     | Y   | 11-Oct      |  29384 |
+----+----------+-----+-------------+--------+
2
  • 2
    Calculate ROW_NUMBER() OVER (PARTITION BY id, addr, ind ORDER BY update_date DESC) in CTE and select only records where the calculated value is 1.
    – Akina
    Oct 12, 2018 at 5:34
  • I think your description is not clear. Do you want the following: Divide the rows of a table into groups. A group should consist of all rows that have the same ID, ADDR and update_Date. From each group select an arbitrary row whose update_Date is equal to the maximal update_Date of its group.
    – miracle173
    May 5, 2020 at 5:09

5 Answers 5

1

Query below gives me the desired result :

SELECT  
       ID,
       ADDR,
       IND,
       UPDATE_DATE,
       SQ_NBR           
FROM (
SELECT ID,
       ADDR,
       IND,
       UPDATE_DATE,
       SQ_NBR,
       ROW_NUMBER() OVER (PARTITION BY ID , ADDR , IND ORDER BY UPDATE_DATE DESC) RMN
FROM TEST_TABLE ) A
WHERE RMN = 1
0

For the first condition

  • count() Over (Partition by id, addr, Ind)
  • max(update_date) Over (Partition by id, addr, Ind)

For the secontd condition

  • row_number Over (Partition by id, addr, Ind and update_Date)
0

Inner "group by" solves the second condition of a question, and outer plus "max (sq_nbr) keep.." works for the first condition.

HR@mydb> select id, addr, ind, max(update_date),
  2         max (sq_nbr) keep (dense_rank last order by update_date) AS sq_nbr
  3    from
  4  (select id, addr, ind, update_date, min(sq_nbr) sq_nbr
  5    from orgin
  6   group by id, addr, ind, update_date)
  7   group by id, addr, ind
  8   order by id, addr desc
  9  /

    ID ADDR            I MAX(UPDATE_DATE)    SQ_NBR
---------- --------------- - ------------------- ----------
     1 PHYSICAL        Y 11.10.2018 00:00:00 1293
     1 BILL            N 10.09.2018 00:00:00 13567
     1 BILL            Y 10.10.2018 00:00:00 1357
     2 PHYSICAL        Y 19.10.2018 00:00:00 5333
     2 BILL            N 08.08.2018 00:00:00 657
     2 BILL            Y 10.10.2018 00:00:00 2574
     3 BILL            Y 11.10.2018 00:00:00 29384

7 rows selected.

The definition of the First function - max() over keep ... is here https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions065.htm#i1000901

0

LAST_VALUE() comes to mind. Documentation is here.

select
  last_value(id) over (partition by id,addr,ind order by update_date) as id,
  last_value(addr) over (partition by id,addr,ind order by update_date) as addr,
  last_value(ind) over (partition by id,addr,ind order by update_date) as ind,
  last_value(update_date) over (partition by id,addr,ind order by update_date) as update_date,
  last_value(sq_nbr) over (partition by id,addr,ind order by update_date) as sq_nbr
from orgin

But, if you upgrade to a current version of Oracle (12c+), you can use MATCH_RECOGNIZE statement. Example is here.

select a.*
from orgin
  MATCH_RECOGNIZE (
    partition by id,addr,ind
    order by update_date
    measures
      last( anyrow.update_date ) as update_date,
      last( anyrow.sq_nbr) as sq_nbr
    one row per match
    pattern (anyrow+)
    define
      anyrow as 1=1
    ) a;

enter image description here

-1

I would suggest this:

select ID, ADDR, IND, Update_Date, Sq_NBR
from yourTable as yt
inner join
    (select max(Update_Date) as newestDate, ID, ADDR, IND
     from yourTable
     group by ID, ADDR, IND
    ) as newTable
on yt.ID = newTable.ID
and yt.ADDR = newTable.ADDR
and yt.IND = newTable.IND
and yt.Update_Date = newTable.newestDate
order by ID, ADDR
2
  • 1
    no, that is not correct. this may select more than one record per group
    – miracle173
    May 5, 2020 at 5:13
  • Thanks, I'll work on it with an actual dataset. My GROUP BY clause is probably off here..
    – grego
    May 6, 2020 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.