RE: SQL to retrieve all distinct records
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Apr 2004 15:55:22 +0200
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Alison Barak
Sent: Thursday, April 15, 2004 14:38
To: oracle-l@xxxxxxxxxxxxx
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@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
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
