Disregard duplicates based on a field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mBird - 27 Apr 2006 19:17 GMT

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!

Reply to this Message

Michel Cadot - 27 Apr 2006 19:47 GMT

|I have a table with three fields:
| f1, f2, f3
[quoted text clipped - 14 lines]
|
| 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

Reply to this Message

Ed Prochak - 29 Apr 2006 00:29 GMT

> I have a table with three fields:
> f1, f2, f3
[quoted text clipped - 14 lines]
>
> Thank you!

what's wrong with:

select f1, MIN(f2), f3 from yourtable
group by f1,f3;

???

Reply to this Message

Michel Cadot - 29 Apr 2006 07:14 GMT

| > I have a table with three fields:
| > f1, f2, f3
[quoted text clipped - 21 lines]
|
| ???

I think, as the OP explaination says it but as his exemple does not show it,
that if you have "3 a 345" and "3 b 346" only the first one have to be displayed.

Btw, he does not tell anything if he has "3 b 345" and "3 c xxx".

Regards
Michel Cadot

Reply to this Message