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.

As it should.

>...  now

Think a moment about what you are asking. This is a relational database, there is no such concept as "first row" in relational systems. Define what row you really want returned. Would you be truely happy with random data returned in the other columns?
>
> 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...

If you really want to return anything for the other columns, try a GROUP BY and use MIN or MAX on the other columns.

SELECT key1,key2, min(other1), max(other2) from ...
GROUP BY key1,key2;

That is just as valid as the "first row" description.

Use the right tool for the job is just all I'm saying.

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost