db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: how to suppress similar rows while copying from one table to another?
Date Fri, 16 Nov 2007 13:15:03 GMT
Amir Michail wrote:
> Hi,
>
> I would like to copy rows from one table to another while copying
> only the first row from sets of similar rows.
>
> In particular, for rows in which two particular fields are the same, I would
> like to copy only the first row in that set.  The rows have more than these
> two fields.
>
> I know how to copy rows from one table to another, but how do I suppress
> similar rows in this way?
>
> Amir
>   
Hi Amir,

One way to solve this problem would be to put a filtering function in 
the WHERE clause of the driving SELECT statement. Something like this:

INSERT INTO targetTable
SELECT * FROM sourceTable
WHERE isFirstInstance( tastyColumn1, tastyColumn2 ) = 1

Here isFirstInstance is a function which returns 1 the first time it 
sees a given key combination and returns 0 on all subsequent sightings. 
You, of course, have to write this user function.

Hope this helps,
-Rick

Mime
View raw message