Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL to retrieve all distinct records

RE: SQL to retrieve all distinct records

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Thu, 15 Apr 2004 15:55:22 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNAEIDCCAA.lex.de.haan@naturaljoin.nl>


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



MSN Toolbar provides one-click access to Hotmail from any Web page FREE download! http://toolbar.msn.com/go/onm00200413ave/direct/01/

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Thu Apr 15 2004 - 09:09:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US