Stuck With Analytic Function

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 17 Oct 2006 01:51:41 -0700
Message-ID: <1161075101.322324.35880@h48g2000cwc.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 Received on Tue Oct 17 2006 - 03:51:41 CDT