| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL to retrieve all distinct records
Alison,
your question is not precise enough. looking at the result you would like to
see,
you have an implicit assumption about ordering of rows sharing the unique
(A,B) combination.
since rows in tables are not having any ordering, it is not clear which of
those rows you want to see.
you should use windowing here, and/or aggregate functions.
what about "select a,b, min(c) from your_table group by a,b" for a start?
Cheers,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Alison Barak
Sent: Thursday, April 15, 2004 14:38
To: oracle-l_at_freelists.org
Subject: SQL to retrieve all distinct records
Hi,
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?
Thanks.
alison
 |
 |