1

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 |
+----+----------+-----+-------------+--------+
  • 1
    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 '18 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 at 5:09
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)
| improve this answer | |
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

| improve this answer | |
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

| improve this answer | |
-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
| improve this answer | |
  • 1
    no, that is not correct. this may select more than one record per group – miracle173 May 5 at 5:13
  • Thanks, I'll work on it with an actual dataset. My GROUP BY clause is probably off here.. – grego May 6 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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