The question you have to answer first is: suppose I have several values of C for the A and B combination Which value of C do I want to return? The one with the lowest value? The one with the highest value? Any one (it doesn't matter which?) e.g. in your example below: A B C ---------- ---------- ---------- APPS APPS R1 SYS SYS2 R2 SYS SYS2 R3 ... Why do you want to return SYS - SYS2 - R2 and not SYS - SYS2 - R3? Does it have to be SYS - SYS2 - R2 or does it matter if the query returns only one row with SYS - SYS2 - R2? Once you answer that question, then the expert minds on this list will help you devise a better query. Without knowing the answer to that question, I could suggest select a, b, min (c) group by a, b ; or select a, b, max (c) group by a, b ; > -----Original Message----- > Alison Barak > > I believe I have found a solution. Just wonder if anyone else has an > alternative or better approach? > > select * from testing where rowid in (select min(rowid) from > testing group > by a,b); > > > >From: "Alison Barak" <ambarak@xxxxxxxxxxx> > > > >can someone assist me with formulating a query to retrieve > all distinct > >records. I need to retrieve all three columns a,b &c based > on the distinct > >of the two columns a & b. > > > >SQL> select * from testing; > >A B C > >---------- ---------- ---------- > >APPS APPS R1 > >SYS SYS2 R2 > >SYS SYS2 R3 > >DOC DOC2 R4 > >DC DC2 R5 > >DOC DOC R6 > > > >The result should be > >A B C > >---------- ---------- ---------- > >APPS APPS R1 > >SYS SYS2 R2 > >DOC DOC2 R4 > >DC DC2 R5 > >DOC DOC R6 > > > > > >I tried using group by/having count but was not successful. Any idea? ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------