Home » SQL & PL/SQL » SQL & PL/SQL » Get most recent Value in one row only. (6 merged) (ORACLE)
| Get most recent Value in one row only. (6 merged) [message #689980] |
Wed, 11 September 2024 09:06  |
 |
Nicha
Messages: 34
Registered: March 2020
|
Member |
|
|
I have a table with accounts and their respective interest rate values (JUR) and bank spread values (SPR) information. For each account I have the history of the rate information by date (DINIVALI - date field). I want to pull the most recent interest rate (JUR) and spread (SPR) information per account.
The CCONDLIQ field has 2 Values : (JUR) for interest rate values; (SPR) for Spread values.
I need to obtain, in a single record, both the most recent interest rate (JUR) and Spread (SPR) associated with the account, as shown in the Query Result.
Table data:
CREATE TABLE Table1
(
CCONTA VARCHAR2(15) NOT NULL,
CCONDLIQ VARCHAR2(3) NOT NULL,
DINIVALI DATE NOT NULL,
VTXJURO NUMBER(8,5) NOT NULL
)
insert into Table1 values ('000315394751096','JUR',To_date('2022-09-02','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','JUR',To_date('2022-09-03','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','JUR',To_date('2023-03-03','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','JUR',To_date('2023-09-03','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','JUR',To_date('2024-03-03','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','JUR',To_date('2024-07-03','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','SPR',To_date('2022-02-09','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','SPR',To_date('2022-09-03','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','SPR',To_date('2023-03-03','YYYY-MM-DD'));
insert into Table1 values ('000315394751096','SPR',To_date('2024-07-03','YYYY-MM-DD'));
Can anyone help me, please?
|
|
|
|
|
|
| Re: Get most recent Value in one row only. (6 merged) [message #689997 is a reply to message #689996] |
Wed, 11 September 2024 13:39  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Another way, maybe faster in your case:
SQL> select *
2 from (select CCONTA, CCONDLIQ,
3 max(VTXJURO) keep(dense_rank last order by DINIVALI) VTXJURO
4 from table1
5 group by CCONTA, CCONDLIQ)
6 pivot (max(VTXJURO) for CCONDLIQ in ('JUR' as "VTXJURO_JUR",'SPR' as "VTXJURO_SPR"))
7 /
CCONTA VTXJURO_JUR VTXJURO_SPR
--------------- ----------- -----------
000315394751096 2.9 0[Updated on: Wed, 11 September 2024 13:40] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Get most recent Value in one row only. (6 merged) [message #690001 is a reply to message #690000] |
Thu, 12 September 2024 10:15 |
|
Nicha
Messages: 34
Registered: March 2020
|
Member |
|
|
Sorry @Michel Cadot.
I've been testing your Query, and notest that it's not working in the example Below: Your Query is ggiving Null values on SPR.
CREATE TABLE Table1
(
CCONTA VARCHAR2(15) NOT NULL,
CCONDLIQ VARCHAR2(3) NOT NULL,
DINIVALI DATE NOT NULL,
VTXJURO NUMBER(8,5) NOT NULL
)
insert into Table1 values ('000310516085096','JUR',To_date('2023-10-03','YYYY-MM-DD'),6.649);
insert into Table1 values ('000310516085096','JUR',To_date('2022-10-03','YYYY-MM-DD'),4.733);
insert into Table1 values ('000310516085096','SPR',To_date('2022-10-03','YYYY-MM-DD'),2.5);
insert into Table1 values ('000310516085096','JUR',To_date('2021-10-03','YYYY-MM-DD'),2.008);
insert into Table1 values ('000310516085096','JUR',To_date('2020-10-03','YYYY-MM-DD'),2.085);
insert into Table1 values ('000310516085096','JUR',To_date('2019-10-03','YYYY-MM-DD'),2.161);
insert into Table1 values ('000310516085096','JUR',To_date('2018-10-03','YYYY-MM-DD'),2.334);
insert into Table1 values ('000310516085096','JUR',To_date('2017-10-03','YYYY-MM-DD'),2.332);
insert into Table1 values ('000310516085096','JUR',To_date('2016-09-30','YYYY-MM-DD'),2.452);
insert into Table1 values ('000310516085096','SPR',To_date('2016-09-30','YYYY-MM-DD'),2.5);
Sorry. Can you take a look please?
Thank you
|
|
|
|
| Re: Get most recent Value in one row only. (6 merged) [message #690002 is a reply to message #690001] |
Thu, 12 September 2024 11:04 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Use the second query (which is better... and correct  ):
SQL> select *
2 from (select CCONTA, CCONDLIQ, max(DINIVALI) DINIVALI,
3 max(VTXJURO) keep(dense_rank last order by DINIVALI) VTXJURO
4 from table1
5 group by CCONTA, CCONDLIQ)
6 pivot (max(VTXJURO) as "VTXJURO", max(DINIVALI) as "DINIVALI" for CCONDLIQ in ('JUR','SPR'))
7 /
CCONTA 'JUR'_VTXJURO 'JUR'_DINIV 'SPR'_VTXJURO 'SPR'_DINIV
--------------- ------------- ----------- ------------- -----------
000310516085096 6.649 03-OCT-2023 2.5 03-OCT-2022[Updated on: Thu, 12 September 2024 11:08] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Get most recent Value in one row only. (6 merged) [message #690008 is a reply to message #690007] |
Thu, 12 September 2024 14:57 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
With the correct ID both work:
SQL> select *
2 from (select CCONTA, CCONDLIQ, max(DINIVALI) DINIVALI,
3 max(VTXJURO) keep(dense_rank last order by DINIVALI) VTXJURO
4 from Table1
5 where cconta = '000310516085096'
6 group by CCONTA, CCONDLIQ
7 -- having cconta = '00031051608509'
8 )
9 pivot (max(VTXJURO) as "VTXJURO", max(DINIVALI) as "DINIVALI" for CCONDLIQ in ('JUR','SPR'))
10 /
CCONTA 'JUR'_VTXJURO 'JUR'_DINIV 'SPR'_VTXJURO 'SPR'_DINIV
--------------- ------------- ----------- ------------- -----------
000310516085096 6.649 03-OCT-2023 2.5 03-OCT-2022
1 row selected.
SQL> select *
2 from (select CCONTA, CCONDLIQ, max(DINIVALI) DINIVALI,
3 max(VTXJURO) keep(dense_rank last order by DINIVALI) VTXJURO
4 from Table1
5 --where cconta = '00031051608509'
6
7 group by CCONTA, CCONDLIQ
8 having cconta = '000310516085096'
9 )
10 pivot (max(VTXJURO) as "VTXJURO", max(DINIVALI) as "DINIVALI" for CCONDLIQ in ('JUR','SPR'))
11 /
CCONTA 'JUR'_VTXJURO 'JUR'_DINIV 'SPR'_VTXJURO 'SPR'_DINIV
--------------- ------------- ----------- ------------- -----------
000310516085096 6.649 03-OCT-2023 2.5 03-OCT-2022
1 row selected. |
|
|
|
| Re: Get most recent Value in one row only. (6 merged) [message #690009 is a reply to message #690008] |
Fri, 13 September 2024 03:40 |
|
Nicha
Messages: 34
Registered: March 2020
|
Member |
|
|
Good morning @Michel Cadot
GREAAATTTTT JOB.
Thank you very much.
Can I Ask you, please, a short explanation on how this work's?
Wich is better to use, between Where or Having Clauses?
Or, at least, how the Query creates the aliases ['JUR'_VTXJURO] and ['SPR'_VTXJURO], as they are not in the select clause?
The idea is Substitute de Select * on the outer query, by Select with the field names.
Most apretiated.
P.S. Can you also tell me how can I close this Topic?
[Updated on: Fri, 13 September 2024 07:52] Report message to a moderator |
|
|
|
| Re: Get most recent Value in one row only. (6 merged) [message #690010 is a reply to message #690009] |
Sat, 14 September 2024 03:38 |
|
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Can I Ask you, please, a short explanation on how this work's?
The inner part returns, for each CCONTA, CCONDLIQ the last date ("max(DINIVALI)" and the VTXJURO value for this one ("max(VTXJURO) keep(dense_rank last order by DINIVALI)"):
SQL> select CCONTA, CCONDLIQ, max(DINIVALI) DINIVALI,
2 max(VTXJURO) keep(dense_rank last order by DINIVALI) VTXJURO
3 from table1
4 group by CCONTA, CCONDLIQ
5 /
CCONTA CCO DINIVALI VTXJURO
--------------- --- ----------- ----------
000310516085096 JUR 03-OCT-2023 6.649
000310516085096 SPR 03-OCT-2022 2.5
2 rows selected.
Quote:Wich is better to use, between Where or Having Clauses?
Theoretically, WHERE is better as it filters the rows before applying the grouping and computing the SELECT expressions when with HAVING you first compute these values for all groups and then restrict the groups to those indicated by the HAVING clause.
Actually, Oracle optimizer is smart enough to convert the HAVING clause into a WHERE one when appropriate which is the case here.
Quote:how the Query creates the aliases ['JUR'_VTXJURO] and ['SPR'_VTXJURO]
The aliases are created by Oracle from the PIVOT clause: the first part of the aliases are from the IN part of this clause and the second part from the aggregate expressions of the PIVOT clause:
SQL> select *
2 from (select CCONTA, CCONDLIQ, max(DINIVALI) DINIVALI,
3 max(VTXJURO) keep(dense_rank last order by DINIVALI) VTXJURO
4 from table1
5 group by CCONTA, CCONDLIQ)
6 pivot (max(VTXJURO) as "VTXJURO", max(DINIVALI) as "DINIVALI"
7 for CCONDLIQ in ('JUR' as "JUR",'SPR' as "SPR"))
8 /
CCONTA JUR_VTXJURO JUR_DINIVAL SPR_VTXJURO SPR_DINIVAL
--------------- ----------- ----------- ----------- -----------
000310516085096 6.649 03-OCT-2023 2.5 03-OCT-2022
SQL> select *
2 from (select CCONTA, CCONDLIQ, max(DINIVALI) DINIVALI,
3 max(VTXJURO) keep(dense_rank last order by DINIVALI) VTXJURO
4 from table1
5 group by CCONTA, CCONDLIQ)
6 pivot (max(VTXJURO) as "agg1", max(DINIVALI) as "agg2"
7 for CCONDLIQ in ('JUR' as "val1", 'SPR' as "val2"))
8 /
CCONTA val1_agg1 val1_agg2 val2_agg1 val2_agg2
--------------- ---------- ----------- ---------- -----------
000310516085096 6.649 03-OCT-2023 2.5 03-OCT-2022
Quote:The idea is Substitute de Select * on the outer query, by Select with the field names.
You can of course rename the Oracle aliases as you want in the outer SELECT clause.
Quote: Can you also tell me how can I close this Topic?
There is no way to close a topic in OraFAQ.
|
|
|
|
| Re: Get most recent Value in one row only. (6 merged) [message #690012 is a reply to message #690010] |
Sat, 14 September 2024 09:30 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Match recognize solution:
select cconta,
jur_vtxjuro vtxjuro_jur,
jur_dinivali date_jur,
spr_vtxjuro vtxjuro_spr,
spr_dinivali date_spr
from table1
match_recognize(
partition by cconta,
ccondliq
order by dinivali
measures
last(vtxjuro) vtxjuro,
last(dinivali) dinivali
one row per match
pattern(p+)
define p as 1 = 1
)
pivot(
max(vtxjuro) vtxjuro,
max(dinivali) dinivali
for ccondliq in ('JUR' jur,'SPR' spr)
)
/
CCONTA VTXJURO_JUR DATE_JUR VTXJURO_SPR DATE_SPR
--------------- ----------- --------- ----------- ---------
000315394751096 2.9 03-JUL-24 0 03-JUL-24
SQL>
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 00:15:27 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
