Re: Select Distinct with LOB fields

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 foo.pk, foo.data from foo, bar where foo.pk = bar.fk

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 foo.pk

hope that makes it clearer.

Andy

<fitzjarrell_at_cox.net> wrote in message
news:1189004917.333123.203360_at_w3g2000hsg.googlegroups.com...
> Comments embedded.
> On Sep 5, 9:46 am, "Andy Fish" <ajf..._at_blueyonder.co.uk> 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