Re: JOINS and DISTINCT...

Marc Gutschner wrote:

> 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 ... do what Alan suggested or use an inline view.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)