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
- If record has a combination of duplicate id, addr and Ind, then select the max(update_date) from that record.
- 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 |
+----+----------+-----+-------------+--------+
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.