Re: Select Distinct with LOB fields

On Sep 5, 10:54 am, "Andy Fish" <ajf..._at_blueyonder.co.uk> wrote:
> 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
>
> <fitzjarr..._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- Hide quoted text -
>
> - Show quoted text -

select pk, data from foo where pk in (select fk from bar);

David Fitzjarrell Received on Wed Sep 05 2007 - 11:44:35 CDT