Dan Bolser <[EMAIL PROTECTED]> wrote on 05/24/2005 06:08:32 AM:
>
> Hello,
>
> I have data like this
>
> PK GRP_COL
> 1 A
> 2 A
> 3 A
> 4 B
> 5 B
> 6 B
> 7 C
> 8 C
> 9 C
>
>
> And I want to write a query to select data like this...
>
> PK FK GRP_COL
> 1 1 A
> 2 1 A
> 3 1 A
> 4 4 B
> 5 4 B
> 6 4 B
> 7 7 C
> 8 7 C
> 9 7 C
>
>
> Where FK is a random (or otherwise) member of PK from within the
> appropriate group given by GRP_COL. FK recreates the grouping from
> GRP_COL, but in terms of PK. I want to do this because GRP_COL is
> difficult to handle and I want to re-represent the grouping in terms of
PK
> (this allows me to link data into the grouping more easily).
>
> Is there a simple way to do this?
>
>
I don't understand your choice of column name for your new column. PK
generally means PRIMARY KEY and FK generally means FOREIGN KEY. FOREIGN
KEYs are used to enforce relational data integrity between tables. What it
looks like you want to do is to tag every row in a group with the lowest
(minimum) PK value for that group. To me, that is not a FK.
To do what you want will either take a subquery or a separate table. I
think the separate table will perform faster so I will show you that
method.
CREATE TEMPORARY TABLE tmpPK (KEY GRP_COL)
SELECT GRP_COL,MIN(PK) as minpk
FROM datatable
GROUP BY GRP_COL;
ALTER TABLE datatable ADD COLUMN FK INT;
UPDATE datatable
INNER JOIN tmpPK
ON tmpPK.GRP_COL = datatable.GRP_COL
SET datatable.FK = tmpPK.minpk;
DROP TEMPORARY TABLE tmpPK;
The slowest part of all of this will be adding the column to your table.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
