Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Select Distinct with LOB fields

Re: Select Distinct with LOB fields

From: Andy Fish <>
Date: Wed, 05 Sep 2007 15:54:47 GMT
Message-ID: <bjADi.60341$>

ok, maybe I didn't phrase the question very well as everyone came up with similar responses. consider this scenario:

create table foo (pk integer, data clob) create table bar (pk integer, fk integer)

select, from foo, bar where =

because several rows in bar might join to the same row in foo, this query could return duplicate rows

i cannot use select distinct because I need to return all columns from foo

because I am only returning columns from foo, I know that uniqueness could be determined by checking only

hope that makes it clearer.


<> wrote in message
> Comments embedded.
> On Sep 5, 9:46 am, "Andy Fish" <> wrote:
>> Hi,
>> I want to do a "Select distinct" including a table which has LOB columns
>> and
>> I see from the Oracle documentation that this is not possible.
> In releases lower than 11g that is correct, as BLOB and BFILE support
> is available in SQL*Plus.
>> all of my tables have a unqiue primary key which is an integer and is
>> included in the select target list. is there any way to tell oracle that
>> it
>> only needs to use the PK field to test for uniqueness and doesn't have to
>> compare the whole returned row?
> Why are you then using 'select distinct'? It's redundant, as the
> inclusion of the primary key column ENSURES each row is unique.
>> my current favourite solution is to post-filter the results in my C# code
>> to
>> remove duplicates manually. does anyone have any better solutions than
>> that
>> ?
> You have a primary key included in the results (according to your
> post) so there shouldn't be any duplicates. Unless, of course, you
> haven't properly explained your 'problem' and expect others to read
> your mind with respect to your actions.
>> TIA
>> Andy
> Post EXACTLY what you're doing and what errors you're seeing, along
> with why you think you need distinct when the primary key is included
> in the result set.
> David Fitzjarrell
Received on Wed Sep 05 2007 - 10:54:47 CDT

Original text of this message