17 messages in com.mysql.lists.mysqlRe: Query to select...
FromSent OnAttachments
Dan Bolser24 May 2005 03:08 
mfat...@free.fr24 May 2005 05:56 
SGr...@unimin.com24 May 2005 06:22 
Dan Bolser24 May 2005 06:23 
Michael Stassen24 May 2005 06:38 
Michael Stassen24 May 2005 07:25 
mfat...@free.fr24 May 2005 07:39 
SGr...@unimin.com24 May 2005 07:56 
Dan Bolser24 May 2005 08:44 
Dan Bolser24 May 2005 16:45 
mfat...@free.fr24 May 2005 22:42 
mfat...@free.fr24 May 2005 23:01 
mfat...@free.fr24 May 2005 23:19 
SGr...@unimin.com25 May 2005 06:31 
Hendro Suryawan25 May 2005 12:09 
Hendro Suryawan25 May 2005 15:23 
Hendro Suryawan26 May 2005 08:25 
Subject:Re: Query to select...
From:SGr...@unimin.com (SGr@unimin.com)
Date:05/24/2005 06:22:35 AM
List:com.mysql.lists.mysql

Dan Bolser <dm@mrc-dunn.cam.ac.uk> 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