Stuck With Analytic Function
pankaj_w...@yahoo.co.in
I have a table structure and values as (TABLE1: dummy)
REF_NO SYMBOL ID EXEC_NAME
--------- -------------------- ---------- --------------------
1 AA 123 DL
1 AA 123 RR
2 BB 133 DL
I want to rank these records based on exec_name.
What I want is, if for a particular ID there is an EXEC_NAME
IN('RR','RK) then that record should
be ranked as 1 and others can be ranked accordingly.
So I want the output as such
REF_NO SYMBOL ID EXEC_NAME
RANK
--------- -------------------- ---------- --------------------
----------
1 AA 123 DL
2
1 AA 123 RR
1
2 BB 133 DL
1
So far I am just able to get the rank to each record based on ID
(without EXEC_NAME condition).
SELECT REF_NO, SYMBOL, ID, EXEC_NAME, RANK
FROM (SELECT REF_NO, SYMBOL, ID, EXEC_NAME, ROW_NUMBER() OVER(PARTITION
BY ID ORDER BY EXEC_NAME
FROM TABLE1)
REF_NO SYMBOL ID EXEC_NAME
RANK
--------- -------------------- ---------- --------------------
----------
1 AA 123 DL
1
1 AA 123 RR
2
2 BB 133 DL
1
How should I modify this to acquire the desired result?
Any help would be appreciated
TIA
DB Version Information:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
Michel Cadot
<pankaj_w...@yahoo.co.in> a écrit dans le message de news: 1161075086.5...@m73g2000cwd.googlegroups.com...
Use a DECODE/CASE in your ORDER BY clause to make RR/RK appear in first.
Use RANK() function instead of ROW_NUMBER() if you want a... rank.
It is useless to use a subquery.
Regards
Michel Cadot
pankaj_w...@yahoo.co.in
Michel Cadot wrote:
Thanks Michael.
Actually in my case ROW_NUMBER will work fine cause I just want to
assign sr.no's to group of records.
I'll be more glad if u can help me in preparing my DECODE function
here.
I am unable to construct the same.
I mean how can I make records with RR/RK to appear first in order and
then apply the ROW_NUMBER
analytic function on that?
Michel Cadot
<pankaj_w...@yahoo.co.in> a écrit dans le message de news: 1161152101....@m7g2000cwm.googlegroups.com...
Michel Cadot wrote:
--------------------------------------
"order by case when exec_name in ('RR','RK') then chr(0) else exec_name end"
then RR and RK will always appears first.
What if you have RR and RK at the same time, or duplicate exec_name?
This is why I think rank or dense_rank _may_ be better, depending on
what rank value you want in these cases.
Regards
Michel Cadot
pankaj_w...@yahoo.co.in
Michel Cadot wrote:
Thanks Michael, tht was beautiful and worked as required.
Also as u suggested, the subquery used was also removed.
Even I had the doubt what if both RR and RK appears at the same time.
The reason I went for this was, till now's data available, this was
never been a case
but I asked my superior and he still needs to get back on this.
Thanks for the help.
pankaj_w...@yahoo.co.in
pankaj_wolfhun...@yahoo.co.in wrote:
One question, how exactly char(0) is working here?
I mean i could have never thought of going this way.
Can you explain ur query?
Michel Cadot
<pankaj_w...@yahoo.co.in> a écrit dans le message de news: 1161187882.8...@m73g2000cwd.googlegroups.com...
pankaj_wolfhun...@yahoo.co.in wrote:
------------------------------------
chr(0) is the character with code point 0, so there is no character before.
If you want you can use a space instead if chr(0) looks too strange for you.
Regards
Michel Cadot
pankaj_w...@yahoo.co.in
Michel Cadot wrote:
Thanks