Home » SQL & PL/SQL » SQL & PL/SQL » Getting a ranked element from an array
Getting a ranked element from an array [message #183800] Sun, 23 July 2006 23:10 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I would like to get the element in my array with the latest transaction effective date. But my query below is not working. Anyone, help please...
            
select client_id,
       trx_effective_date
       rank() OVER(PARTITION BY NULL ORDER BY trx_effective_date desc) "ranking"                         
  into v_client_id,
       v_trx_eff_dt
       v_rank
  from table (cast (client_tab1 as typ_client_tab))
 where "ranking" = 1;

[Updated on: Sun, 23 July 2006 23:11]

Report message to a moderator

Re: Getting a ranked element from an array [message #183801 is a reply to message #183800] Sun, 23 July 2006 23:24 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try with the code ..

Select client_id,trx_effective_date,ranking 
From(select client_id,
            trx_effective_date
            rank() OVER(PARTITION BY NULL ORDER BY trx_effective_date desc) "ranking"                         
     From table (cast (client_tab1 as typ_client_tab)))
into v_client_id,
     v_trx_eff_dt
     v_rank
where ranking = 1;   


Still error persists , provide what type of error you are getting ..

Thumbs Up
Rajuvan.



[Updated on: Sun, 23 July 2006 23:25]

Report message to a moderator

Re: Getting a ranked element from an array [message #183817 is a reply to message #183801] Mon, 24 July 2006 01:18 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Select client_id,trx_effective_date,ranking 
into v_client_id,
     v_trx_eff_dt
     v_rank
From(select client_id,
            trx_effective_date
            rank() OVER(PARTITION BY NULL ORDER BY trx_effective_date desc) "ranking"                         
     From table (cast (client_tab1 as typ_client_tab)))
where ranking = 1;   


Yes it worked.. but is there a way not to use subquery??
Re: Getting a ranked element from an array [message #183835 is a reply to message #183817] Mon, 24 July 2006 02:00 Go to previous messageGo to next message
rajavu
Messages: 11
Registered: March 2005
Location: Bangalore
Junior Member

No .. (I am not aware of such option .. )

Thumbs Up
Rajuvan.
Re: Getting a ranked element from an array [message #183851 is a reply to message #183835] Mon, 24 July 2006 02:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT MAX(client_id) KEEP (DENSE_RANK LAST ORDER BY trx_effective_date)
,MAX(trx_effective_date)
FROM table (cast (client_tab1 as typ_client_tab)))
INTO v_client_id,
     v_trx_eff_dt


Ross Leishman
Re: Getting a ranked element from an array [message #183910 is a reply to message #183851] Mon, 24 July 2006 06:12 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Yes .. ... There you are !!!!

Rajuvan.
Re: Getting a ranked element from an array [message #183914 is a reply to message #183851] Mon, 24 July 2006 06:37 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, I've learned something today. Cool

Good solution
Previous Topic: help with pl/sql code
Next Topic: Concatenation of Conditions in Where Clause in Procedure
Goto Forum:
  


Current Time: Wed Dec 07 14:33:35 CST 2016

Total time taken to generate the page: 0.09300 seconds