Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Stuck With Analytic Function

Re: Stuck With Analytic Function

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 18 Oct 2006 19:02:34 +0200
Message-ID: <45365e2a$0$2657$426a74cc@news.free.fr>

<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 Received on Wed Oct 18 2006 - 12:02:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US