You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Amir Michail <am...@gmail.com> on 2007/11/16 02:02:29 UTC
how to suppress similar rows while copying from one table to another?
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
Re: how to suppress similar rows while copying from one table to
another?
Posted by Rick Hillegas <Ri...@Sun.COM>.
Dag H. Wanvik wrote:
> Daniel John Debrunner <dj...@apache.org> writes:
>
>
>> Rick Hillegas wrote:
>>
>>
>>> 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.
>>>
>> Any thoughts on how to do that? Since such calls are stateless how
>> would you identify it's the first call for a query execution?
>>
Right. I think you could add a context handle as a third argument to the
function. Then you could request a context handle before running the
query and release the context after the query. So now the invocation
looks like this:
VALUES ( getContextHandle() );
INSERT INTO targetTable
SELECT * FROM sourceTable
WHERE isFirstInstance( tastyColumn1, tastyColumn2, contextHandle ) = 1;
CALL releaseContext( contextHandle );
Another solution would be to wrap the SELECT in a filtering table
function. Something like this:
INSERT INTO targetTable
SELECT s.* FROM TABLE( filteringFunction( ) ) s
Regards,
-Rick
Re: how to suppress similar rows while copying from one table to another?
Posted by Amir Michail <am...@gmail.com>.
Hi,
I ended up using JDBC to do this.
Amir
On Nov 16, 2007 8:37 PM, Dag H. Wanvik <Da...@sun.com> wrote:
>
> Daniel John Debrunner <dj...@apache.org> writes:
>
> > Rick Hillegas wrote:
> >
> >> 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.
> >
> > Any thoughts on how to do that? Since such calls are stateless how
> > would you identify it's the first call for a query execution?
>
> I first thought of using a tmp table containing just the two columns,
> and just opening a result set on the first table, loop, and copying a
> row to destination table if not present yet in tmp table, else insert
> it there too. One could hide that check inside a function like Rick
> suggests?
>
> Tried in vain to come up with a clever single INSERT, though.
>
> Dag
>
Re: how to suppress similar rows while copying from one table to another?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Bernt M. Johnsen wrote (2007-11-19 10:23:05):
> If there exists a column k in your source data which is unique and you
> may use the aggregate function MIN on this column you may do something
> like:
>
> INSERT INTO target
> (SELECT source.k, source.col1, source.col2, source.arbitrary FROM
> source,
> (SELECT MIN(k),col1,col2 FROM source
> GROUP BY col1,col2) AS tmp(k,col1,col2)
> WHERE source.k = tmp.k);
Just for the fun of it (and not for the faint of heart), in Derby 10.4
you might use the ROW_NUMBER() windowing function to generate the key
k (if you den't have one), and the expression might be written like
this:
INSERT INTO target
(SELECT s1.k, s2.col1, s2.col2, s2.arbitrary FROM
(SELECT ROW_NUMBER() ORDER by col1,col2 AS k,
col1,col2,arbitraty FROM source)
AS s1(k,col1,col2,arbitrary),
(SELECT MIN(k),col1,col2 FROM
(SELECT ROW_NUMBER() ORDER by col1,col2 AS k,
col1,col2 FROM source)
AS s2(k,col1,col2),
GROUP BY col1,col2) AS tmp(k,col1,col2)
WHERE s1.k = tmp.k);
(Note, I have not tested this since the ROW_NUMBER() implementation is
not yet committed).
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: how to suppress similar rows while copying from one table to another?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Bernt M. Johnsen wrote (2007-11-19 10:23:05):
> Hi,
>
> >>>>>>>>>>>> Dag H. Wanvik wrote (2007-11-17 02:37:27):
> > Tried in vain to come up with a clever single INSERT, though.
>
> Depending on the data, this might be done in SQL with a "clever single
> INSERT".
>
> If there exists a column k in your source data which is unique and you
> may use the aggregate function MIN on this column you may do something
> like:
>
> INSERT INTO target
> (SELECT source.k, source.col1, source.col2, source.arbitrary FROM
> source,
> (SELECT MIN(k),col1,col2 FROM source
> GROUP BY col1,col2) AS tmp(k,col1,col2)
> WHERE source.k = tmp.k);
>
> Note that the term "first" has no meaning in SQL unless you assign
> some kind of order to your data. In this example, the numeric value k
> is considered to define the order, and thus MIN(k) will be the "first"
> value.
If you don't have a unique column k, you may of course create a
intermediate table with one with an generated identity column (This
assumes that you have no preferred order of your rows).
Assume source has four integer columns: col1, col2 and arbitrary, the
whole operation should go like this:
CREATE TABLE source2 (col1 INTEGER, col2 INTEGER, arbitrary INTEGER,
k INTEGER GENERATED ALWAYS AS IDENTITY);
INSERT INTO source2(col1,col2,arbitrary) (SELECT * from SOURCE);
INSERT INTO target
(SELECT source2.col1, source2.col2, source2.arbitrary FROM
source2,
(SELECT MIN(k),col1,col2 FROM source2
GROUP BY col1,col2) AS tmp(k,col1,col2)
WHERE source2.k = tmp.k);
DROP TABLE source2;
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: how to suppress similar rows while copying from one table to another?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Hi,
>>>>>>>>>>>> Dag H. Wanvik wrote (2007-11-17 02:37:27):
> Tried in vain to come up with a clever single INSERT, though.
Depending on the data, this might be done in SQL with a "clever single
INSERT".
If there exists a column k in your source data which is unique and you
may use the aggregate function MIN on this column you may do something
like:
INSERT INTO target
(SELECT source.k, source.col1, source.col2, source.arbitrary FROM
source,
(SELECT MIN(k),col1,col2 FROM source
GROUP BY col1,col2) AS tmp(k,col1,col2)
WHERE source.k = tmp.k);
Note that the term "first" has no meaning in SQL unless you assign
some kind of order to your data. In this example, the numeric value k
is considered to define the order, and thus MIN(k) will be the "first"
value.
Bernt
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: how to suppress similar rows while copying from one table to
another?
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Daniel John Debrunner <dj...@apache.org> writes:
> Rick Hillegas wrote:
>
>> 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.
>
> Any thoughts on how to do that? Since such calls are stateless how
> would you identify it's the first call for a query execution?
I first thought of using a tmp table containing just the two columns,
and just opening a result set on the first table, loop, and copying a
row to destination table if not present yet in tmp table, else insert
it there too. One could hide that check inside a function like Rick
suggests?
Tried in vain to come up with a clever single INSERT, though.
Dag
Re: how to suppress similar rows while copying from one table to
another?
Posted by Daniel John Debrunner <dj...@apache.org>.
Rick Hillegas wrote:
> 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.
Any thoughts on how to do that? Since such calls are stateless how would
you identify it's the first call for a query execution?
Dan.
Re: how to suppress similar rows while copying from one table to
another?
Posted by Rick Hillegas <Ri...@Sun.COM>.
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