Re: Stuck With Analytic Function

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 18 Oct 2006 17:50:49 +0200
Message-ID: <45364d6e$0$15808$426a74cc@news.free.fr>

<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 Received on Wed Oct 18 2006 - 10:50:49 CDT