Re: JOINS and DISTINCT...

"Alan" <alan_at_erols.com> wrote in message news:<2veq68F2l1tejU1_at_uni-berlin.de>...
> "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.

Try concatenating the 2 fields together: select distinct(id || ' ' || name) Received on Thu Nov 11 2004 - 20:07:57 CET