Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!not-for-mail
From: gschiber@hotmail.com (G.Schiber)
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: JOINS and DISTINCT...
Date: 11 Nov 2004 11:07:57 -0800
Organization: http://groups.google.com
Lines: 49
Message-ID: <203df6ae.0411111107.381ecac4@posting.google.com>
References: <cmt583$fn$00$1@news.t-online.com> <2veq68F2l1tejU1@uni-berlin.de>
NNTP-Posting-Host: 67.105.20.18
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1100200077 449 127.0.0.1 (11 Nov 2004 19:07:57 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 11 Nov 2004 19:07:57 +0000 (UTC)
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:229125 comp.databases.oracle.tools:66640

"Alan" <alan@erols.com> wrote in message news:<2veq68F2l1tejU1@uni-berlin.de>...
> "Marc Gutschner" <Marc.Gutschner@ITandFactory.COM> wrote in message
> news:cmt583$fn$00$1@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)