On Tue, 24 May 2005 [EMAIL PROTECTED] wrote: >Selon Dan Bolser <[EMAIL PROTECTED]>: > >> >> 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?
Sorry about the column names above (something in my head). Here is my favorite answer... SET @i=0, @row=''; SELECT *, -- Data table IF(@row=GRP_COL, @i, @i:[EMAIL PROTECTED]) AS FK, @row:=GRP_COL AS DROP_ME_LATER FROM data ORDER BY GRP_COL -- Essential for the logic used ; http://dev.mysql.com/doc/mysql/en/variables.html (John Belamaric) Having the FK column taken from the PK column was clearly not necessary (thanks all again for pointers). Somehow in the distant memory of my brain this is the answer I was looking for (and finally found). I like this answer because I hate that half of SQL which ALTERS tables and I have a neurotic fear of UPDATES accross JOINS that infected my nightmares as a youth! Strange I know, but its late and time for me to sleep /(xOx)/ Pleasant dreams! Dan. >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >> >> > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
