Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!sn-xit-04!sn-xit-12!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: DA Morgan Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools Subject: Re: JOINS and DISTINCT... Date: Thu, 11 Nov 2004 20:16:14 -0800 Organization: Ye 'Ol Disorganized NNTPCache groupie Message-ID: <1100232897.75832@yasure> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@oracle.advtechserv.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 45 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:229147 comp.databases.oracle.tools:66652 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@x.washington.edu (replace 'x' with 'u' to respond)