Re: Disregard duplicates based on a field

"mBird" <no_at_email.com> a écrit dans le message de news: 12522mjn1i3k0d0_at_corp.supernews.com...
|I have a table with three fields:
| f1, f2, f3
| 1 a 432
| 2 b 323
| 3 a 345 <-- so 3 has an a and b entry
| 3 b 345 <-- so 3 has an a and b entry
|
| I need all the distinct f1's but when choosing which of the duplicates to
| keep be able to specify the priority choice of f2 being a. So...
| -if there is only one distinct f1 then I want that row regardless of f2
| -if there are multiple rows with the same f1's then I want the one that f2 =
| a
| 1 a 432
| 2 b 323
| 3 a 345
| (notice I kept 3 a 345 but discarded 3 b 345)
|
| Thank you!
|

with data as (
  select f1, f2, f3,

       row_number () over (partition by f1 order by decode(f2,'a',chr(0),f2)) rn   from mytable )
select f1, f2, f3
from data
where rn=1
/

Regards
Michel Cadot Received on Thu Apr 27 2006 - 13:47:29 CDT