problem building an Oracle SQL

I have come across a scenario I cannot seem to solve with a SQL statement. I could try this as a cursor, but was looking to write a single SQL query so I dont require a framework change to switch to a cursor... it became a good challenger but now I am thinking it cant be done. Any suggestions welcome.

Problem. I have a series of tables joined together of which two of the table contain important keys. Now I only want to distinct on those two keys and diregard the rest of the row.

Eg.

   SELECT distinct t1.Field1 key1, t2,Field1 key2, t1.field3 other1, t4.field2 other2,etc....

      From     table1 t1, table2 t2, table3 t3, table4 t4
      where    t1.Field1 = t2.Field5
               t2.Field4 = t4.field1 etc....
     

The results give me for example.
Key1      Key 2    other1          other2
  1    |     31  |   somedata    |  someotherdata
  1    |     31  |   somedatatoo |  somemoredata
  2    |     45  |   abc         |  def
  2    |     45  |   abd         |  def
  2    |     45  |   abd         |  ddf

etc... Basically the distinct is happening at the row level. now what I want to do is limit the query to return to just one row of each key1/key2 combination, diregarding all but the first row for each pair. So in essence, just a single sample of data for each combintation of the first two fields returned. Questions is can it be done....

I have tried playing with rownum to control this but cant figure it.... also rowid to control the data back from each table.... I just am totally stuck....

so my data I would like to see is

Key1      Key 2    other1          other2
  1    |     31  |   somedata    |  someotherdata
  2    |     45  |   abc         |  def

Also the way the row gets chosen can be any choice.... anyone got any ideas... Received on Tue Jul 13 2004 - 18:39:14 CDT