Re: JOINS and DISTINCT...

[Quoted] "Marc Gutschner" <Marc.Gutschner_at_ITandFactory.COM> wrote in message news:cmt583$fn$00$1_at_news.t-online.com...
> Hi!
>
> Scenario:
>
> Given two tables where one table contains data, the other table contains
> sort of an audit trail with changes made to the data. The audit trail is
> connected to the data table by a column containing the ID of the record in
> the data table.
>
> I'm currently trying to "conjure up" some SQL that will give me an ordered
> list of all IDs from the data table that have some entries in the audit
> trail. The criteria for the ordering might be the ID, the Name of the
Object
> identified by the ID or the sequence ID of the audit trail record. Since I
> only want all the distinct IDs I've ran into some serious difficulties
doing
> so.
>
> If I do
>
> SELECT DISTINCT(d.obj_id) FROM data d, audit_trail a WHERE (d.obj_id =
> a.obj_id) ORDER BY a.name;
>
> I get an ORA-1791 (i.e. Item not in SELECT list)
>
> If I change this to
>
> SELECT DISTINCT(d.obj_id), a.name FROM data d, audit_trail a WHERE
(d.obj_id
> = a.obj_id) ORDER BY a.name;
>
> I get an ordered list, but it contains duplicate IDs if there has been
more
> than one change to a record in the data table...
>
> Is there anything I can do to hack up a query that will produce an ordered
> list of unique IDs as I need them?
>
> MTIA for any help/hints,
> Marc
>
>

Hint: Use a subquery. Received on Wed Nov 10 2004 - 16:24:56 CET