Re: problem building an Oracle SQL

Brian Ballard wrote:

> 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...

Use an in-line view.

SELECT DISTINCT *
FROM (
   SELECT ...
   FROM ..., ...); Daniel Morgan Received on Tue Jul 13 2004 - 19:07:15 CDT