Re: ROWID / subselect problem

Mark wrote:

> Hello,
>
> I have a problem which is baffling me somewhat.
>
> I have a query which includes a join to a table which might have more than 1
> row for the record in question. Therefore, I've tried to eliminate duplicate
> rows by adding in a sub-select which only brings back the 1st row, like
> this:
>
> SELECT ...
> FROM ...
> WHERE ...
> AND table.rowid =
> (SELECT table2.rowid
> FROM table2
> WHERE table2.column = table.column
> AND rownum = 1
> )
>
> This works (i.e. it removes all duplicate rows) for all but 2 cases, where
> no rows are returned. These rows *never* had duplicates in the table, but a
> single row, so they shouldn't be affected by the sub-select.
>
> If I cut the sub-select out and hardcode the join condition, it works fine
> and brings back a rowid. If I hardcode this rowid into my original query, it
> works and brings back 1 row.
>
> Therefore, I'm muchly confused why this should happen.
>
> Thanks for any assistance.
>
> Mark

Based on your statements your problem is that your database design, and I use the word design in its loosest possible interpretation, is not normalized. There is no excuse for duplicate rows anywhere at any time for any reason in any relational database. Clean up your data!

In any case selecting MIN or MAX rowid with a GROUP BY will solve the problem until you clean up the mess.

And by all means clean up the mess. Otherwise this will haunt you for an eternity.

Daniel Morgan Received on Thu Jan 30 2003 - 10:27:07 CST