Re: Stuck With Analytic Function

<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 Received on Tue Oct 17 2006 - 10:58:23 CDT