Home » SQL & PL/SQL » SQL & PL/SQL » Get all installments based on several plans (get unique values based on a single column) (11.2.0.10)
Get all installments based on several plans (get unique values based on a single column) [message #649205] |
Thu, 17 March 2016 05:45 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/04be50cd0e98b78f02d8b82f9456b619?s=64&d=mm&r=g) |
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have a table that stores all installments and their plan. In case of re-scheduling, only re-scheduled installments are moved to the new plan. I need a list of all installements but in case an installement is repeated in more than one plan, get the installment that belongs to the last plan.
The case is as follows:
CREATE table try_plan_inst
(
fk_plan_id NUMBER,
inst_id NUMBER,
inst_date DATE,
inst_amount number
);
INSERT ALL
INTO TRY_PLAN_INST VALUES (1,1,TO_DATE('01-01-2000','DD-MM-YYYY'),1000)
INTO TRY_PLAN_INST VALUES (1,2,TO_DATE('01-01-2001','DD-MM-YYYY'),1000)
INTO TRY_PLAN_INST VALUES (1,3,TO_DATE('01-01-2002','DD-MM-YYYY'),1000)
INTO TRY_PLAN_INST VALUES (1,4,TO_DATE('01-01-2003','DD-MM-YYYY'),1000)
INTO TRY_PLAN_INST VALUES (1,5,TO_DATE('01-01-2004','DD-MM-YYYY'),1000)
INTO TRY_PLAN_INST VALUES (1,6,TO_DATE('01-01-2005','DD-MM-YYYY'),1000)
INTO TRY_PLAN_INST VALUES (2,4,TO_DATE('01-01-2006','DD-MM-YYYY'),1500)
INTO TRY_PLAN_INST VALUES (2,5,TO_DATE('01-01-2007','DD-MM-YYYY'),1500)
INTO TRY_PLAN_INST VALUES (2,6,TO_DATE('01-01-2008','DD-MM-YYYY'),1500)
INTO TRY_PLAN_INST VALUES (3,6,TO_DATE('01-01-2009','DD-MM-YYYY'),1400)
SELECT * FROM DUAL;
--desired output: all installements but in case an installement is repeated in more than one plan, get the installment that belongs to the last plan
fk_plan_id inst_id inst_date inst_amount
1 1 01-01-2000 1000
1 2 01-01-2001 1000
1 3 01-01-2002 1000
2 4 01-01-2006 1500
2 5 01-01-2007 1500
3 6 01-01-2009 1400
Many thanks,
Ferro
|
|
|
|
Re: Get all installments based on several plans (get unique values based on a single column) [message #649212 is a reply to message #649205] |
Thu, 17 March 2016 13:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl> SELECT fk_plan_id, inst_id, inst_date, inst_amount
2 FROM (SELECT fk_plan_id, inst_id, inst_date, inst_amount,
3 DENSE_RANK () OVER (PARTITION BY inst_id ORDER BY fk_plan_id DESC) dr
4 FROM try_plan_inst)
5 WHERE dr = 1
6 ORDER BY fk_plan_id, inst_id
7 /
FK_PLAN_ID INST_ID INST_DATE INST_AMOUNT
---------- ---------- ----------- -----------
1 1 01-JAN-2000 1000
1 2 01-JAN-2001 1000
1 3 01-JAN-2002 1000
2 4 01-JAN-2006 1500
2 5 01-JAN-2007 1500
3 6 01-JAN-2009 1400
6 rows selected.
|
|
|
|
|
Goto Forum:
Current Time: Tue Jun 18 08:14:03 CDT 2024
|