JOINS and DISTINCT...

Scenario:

[Quoted] [Quoted] 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 [Quoted] 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.

[Quoted] 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;

[Quoted] I get an ordered list, but it contains duplicate IDs if there has been more [Quoted] 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 Received on Wed Nov 10 2004 - 14:32:11 CET