Re: Stuck With Analytic Function

Michel Cadot wrote:

> <pankaj_wolfhunter_at_yahoo.co.in> a écrit dans le message de news: 1161187882.835593.128390_at_m73g2000cwd.googlegroups.com...
>
> pankaj_wolfhun..._at_yahoo.co.in wrote:
>
> > Michel Cadot wrote:
> >
> > > <pankaj_wolfhunter_at_yahoo.co.in> a écrit dans le message de news: 1161152101.294926.7750_at_m7g2000cwm.googlegroups.com...
> > >
> > > Michel Cadot wrote:
> > >
> > > > <pankaj_wolfhunter_at_yahoo.co.in> a écrit dans le message de news: 1161075086.591935.160320_at_m73g2000cwd.googlegroups.com...
> > > > | Greetings,
> > > > | 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
> > > > |
> > > >
> > > > 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
> > >
> > > 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?
> > >
> > > --------------------------------------
> > >
> > > "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
> >
> > 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.
>
> 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?
>
> ------------------------------------
>
> 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

Thanks Received on Thu Oct 19 2006 - 02:15:24 CDT