Hi All ,

Anyone help me to write the query :)

****************************
Id | AccountNo.|Status
****************************
1 | A10000 |Y
****************************
1 | A20000 |Null
****************************
1 | A30000 |Null
****************************
2 | B10000 |Null
****************************
2 | B20000 |Null
****************************
2 | B30000 |Null
****************************
3 | C30000 |Null
****************************
3 | C20000 |Null
****************************
3 | C10000 |Null
****************************
4 | D10000 |Null
****************************
5 | E10000 |Y

I want to retrieve (ID ,AccountNo.and Status)

Conditions-

1-->if with particular Id there is "Y" and "Null" i want to retrieve only "Y" records
(i.e in above case 1,A10000,Y)and (5,E10000,Y)

2-->if with particular Id there is no "Y" ,then i want to retrieve only first records with status "Null" records
(i.e in above case 2,B10000,Null) and (2,C30000,Null)Note here C3 comes before C1.

Depending on above table i want to write query that can return following table
****************************
Id | AccountNo.|Status
****************************
1 | A10000 |Y
****************************
2 | B10000 |Null
****************************
3 | C30000 |Null
****************************
4 | D10000 |Null
****************************
5 | E10000 |Y


Awaiting for your reply

Cheers :)

vinod

__________________
Cheers :)

vinod

The below mentioned query,
select * from test_tab where (ID, NVL(Status, 'Z')||ACC_NO) in (
select ID, MIN(NVL(Status, 'Z')||ACC_NO) aa from test_tab group by ID);

Gives result as,

1 1 A10000 Y
2 2 B10000
3 3 C10000
4 4 D10000
5 5 E10000 Y

Now why it does not Give you C30000 (in place of C10000) is, because you will have to give a Orader by statement Before you say "retrieve only first records". Otherwise you cannot say which is the first row.

  Hope this will help you.

Sujit

Hi Sujit,
Thanks i tried your query ,it help me alott!!!
I appreciate your help.


Cheers :)

vinod

select * from test_tab where rowid in(select min(rowid)
from test_tab group by id);
i think you get the required answer .bye

thr